<?php
/**
 * Piwik - Open source web analytics
 * 
 * @link http://piwik.org
 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
 * @version $Id: API.php 268 2013-03-22 06:33:38Z jobarthel@gmail.com $
 * 
 * @category Piwik_Plugins
 * @package Piwik_OXID_Plugin
 */

class Piwik_OxidPlugin_API 
{
	static private $instance = null;
	static public function getInstance()
	{
		if (self::$instance == null)
		{
			self::$instance = new self;
		}
		return self::$instance;
	}
	
	public function getTemperaturesEvolution($date, $period)
	{
		$period = new Piwik_Period_Range($period, 'last30');
		$dateStart = $period->getDateStart()->toString('Y-m-d'); // eg. "2009-04-01"
		$dateEnd = $period->getDateEnd()->toString('Y-m-d'); // eg. "2009-04-30"
		
		// here you could select from your custom table in the database, eg.
		$query = "SELECT AVG(temperature)
					FROM server_temperatures
					WHERE date > ?
						AND date < ?
					GROUP BY date
					ORDER BY date ASC";
		//$result = Piwik_FetchAll($query, array($dateStart, $dateEnd));
		// to keep things simple, we generate the data
		foreach($period->getSubperiods() as $subPeriod)
		{
			$server1 = rand(50,90);
			$server2 = rand(40, 110);
			$value = array('server1' => $server1, 'server2' => $server2);
			$temperatures[$subPeriod->getLocalizedShortString()] = $value;
		}
		
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($temperatures);
		return $dataTable;
	}
	
	// we generate an array of random server temperatures
	public function getTemperatures()
	{
		$xAxis = array(
			'0h', '1h', '2h', '3h', '4h', '5h', '6h', '7h', '8h', '9h', '10h', '11h', 
			'12h', '13h', '14h', '15h', '16h', '17h', '18h', '19h', '20h', '21h', '22h', '23h',
		);
		$temperatureValues = array_slice(range(50,90), 0, count($xAxis));
		shuffle($temperatureValues);
		$temperatures = array();
		foreach($xAxis as $i => $xAxisLabel) {
			$temperatures[$xAxisLabel] = $temperatureValues[$i];
		}
		
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($temperatures);
		return $dataTable;
	}
	
	
	// Retrieving the revenue values from OXID eSales
	public function getRevenue($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
		if ($this->DebugMode) {
			$payments = getOxPayments($this);
			foreach ($payments as $payment)
				logfile('debug', $payment);
		}
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
		/* No longer necessary
                if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
		
		if ($this->DebugMode) logfile('debug', 'period='.$period);
                switch ($period) {

			case 'range':
				// daily revenue
				$sql = 'SELECT o.oxorderdate AS dateval, o.oxordernr as count, '
					 . 'GROUP_CONCAT(CONCAT(\'<nobr>\', a.oxamount,\' x \', a.oxtitle, IF (a.oxselvariant != \'\', \'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\', \'\'), a.oxselvariant, \'</nobr>\') SEPARATOR \'<br>\') AS average, '
					 . 'o.oxtotalordersum AS @SUMVAL@, p.oxdesc AS payment, '
                                         . '@MARGIN@'
                                         . 'CONCAT(\'<a href="mailto:\', o.oxbillemail,\'"><u><nobr>\', o.oxbillfname, \' \', o.oxbilllname, \'</nobr></u></a>\') AS custname, '
                                         . 'IF (o.oxdelcity = \'\', '
                                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxbillstreet,\'+\',o.oxbillstreetnr,\',+\',o.oxbillzip,\'+\',o.oxbillcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxbillzip, \'&nbsp;\', o.oxbillcity, \'</a>\'), '
                                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxdelstreet,\'+\',o.oxdelstreetnr,\',+\',o.oxdelzip,\'+\',o.oxdelcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxdelzip, \'&nbsp;\', o.oxdelcity, \'</a>\') '
                                            . ') AS  custdeladdr, '
                                         . 'IF (o.oxsenddate != \'0000-00-00 00:00:00\', '
                                            . 'IF(o.oxtrackcode != \'\', '
                                                 . 'CONCAT(DATE(o.oxsenddate), \' <a href="\','
                                                        . 'REPLACE('
                                                        . 'REPLACE('
                                                        . 'REPLACE('
                                                        . 'REPLACE('
                                                        . 'REPLACE('
                                                        . 'REPLACE(o.oxtrackcode,'
                                                        . '\'DHL\',CONCAT(\'http://nolp.dhl.de/nextt-online-public/set_identcodes.do?extendedSearch=false&rfn=&searchQuick=Suchen&idc=\')),'
                                                        . '\'ILX\',\'https://www.iloxx.de/net/popup/trackpop.aspx?id=\'),'
                                                        . '\'UPS\',\'http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=de_DE&tracknum=\'),'
                                                        . '\'DPD\',\'http://extranet.dpd.de/cgi-bin/delistrack?typ=1&lang=de&pknr=\'),'
                                                        . '\'GLS\',\'http://www.gls-group.eu/276-I-PORTAL-WEB/content/GLS/DE03/DE/5004.htm?txtAction=71000&txtRefNo=\'),'
                                                        . '\'HMS\',\'http://tracking.hlg.de/Tracking.jsp?TrackID=\'),'
                                                        . '\'" style="text-decoration:underline;" target="_blank">\', o.oxtrackcode, \'</a>\'), '
                                                        . 'DATE(o.oxsenddate) '
                                                . '), '
                                            . '\'-\' '
                                            . ') AS sendstate, '
                                            . 'IF(o.oxremark!=\'\', '
                                                . 'IF((SELECT o.oxremark LIKE \''.$this->IgnoreRemark[$this->SiteID].'\') != 1,'
                                                    . 'CONCAT(\'<img SRC="plugins/OxidPlugin/images/remarks.png" ALT="\', o.oxremark, \'" TITLE="\', o.oxremark, \'" />\'), '
                                                    . '\'\''
                                                . '), '
                                                . '\'\''
                                            . ') AS remark '
					 . 'FROM oxorder o, oxorderarticles a, oxpayments p ' 
					 . 'WHERE  o.oxid = a.oxorderid  '
                                            . 'AND o.oxpaymenttype = p.oxid '
                                            . 'AND DATE(o.oxorderdate) >= \'' . $dateStart . '\' ' 
                                            . 'AND DATE(o.oxorderdate) <= \'' . $dateEnd . '\' '
                                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                            . 'AND o.oxstorno = @STORNOVAL@ '
                                            . 'AND a.oxstorno = @STORNOVAL@ '
					 . 'GROUP BY o.oxordernr '
					 . 'ORDER BY o.oxordernr '; 
				
				$margin = '(SUM(a.oxnetprice) - '
                                            . '(SELECT SUM('
                                                . 'IF(a1.oxparentid=\'\', '
                                                    . 'a1.oxbprice, '
                                                    . 'IF(a1.oxbprice=0.0, (SELECT b2.oxbprice FROM oxarticles b2 WHERE b2.oxid = a1.oxparentid), a1.oxbprice))'
                                                . '*d1.oxamount) '
                                                . 'FROM oxarticles a1, oxorderarticles d1 '
                                                . 'WHERE d1.oxartid = a1.oxid '
                                                    . 'AND o.oxid = d1.oxorderid '
                                                    . 'AND d1.oxstorno = 0)) '
                                            . 'AS netmargin, ';
                                break;

			case 'day':
				$sql = 'SELECT TIME(o.oxorderdate) AS dateval, o.oxordernr as count, '
					 . 'GROUP_CONCAT(\'<nobr>\', CONCAT(a.oxamount,\' x \', a.oxtitle, IF (a.oxselvariant != \'\', \'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\', \'\'), a.oxselvariant, \'</nobr>\') SEPARATOR \'<br>\') AS average, '
					 . 'o.oxtotalordersum AS @SUMVAL@, p.oxdesc AS payment , '
                                         . '@MARGIN@'
                                         . 'CONCAT(\'<a href="mailto:\', o.oxbillemail,\'"><u><nobr>\', o.oxbillfname, \' \', o.oxbilllname, \'</nobr></u></a>\') AS custname, '
                                         . 'IF (o.oxdelcity = \'\', '
                                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxbillstreet,\'+\',o.oxbillstreetnr,\',+\',o.oxbillzip,\'+\',o.oxbillcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxbillzip, \'&nbsp;\', o.oxbillcity, \'</a>\'), '
                                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxdelstreet,\'+\',o.oxdelstreetnr,\',+\',o.oxdelzip,\'+\',o.oxdelcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxdelstreet, \'&nbsp;\', o.oxdelstreetnr, \', \', o.oxdelzip, \'&nbsp;\', o.oxdelcity, \'</a>\') '
                                            . ') AS  custdeladdr, '
                                         . 'IF (o.oxsenddate != \'0000-00-00 00:00:00\', '
                                            . 'IF(o.oxtrackcode != \'\', '
                                                 . 'CONCAT(DATE(o.oxsenddate), \' <a href="\','
                                                        . 'REPLACE('
                                                        . 'REPLACE('
                                                        . 'REPLACE('
                                                        . 'REPLACE('
                                                        . 'REPLACE('
                                                        . 'REPLACE(o.oxtrackcode,'
                                                        . '\'DHL\',CONCAT(\'http://nolp.dhl.de/nextt-online-public/set_identcodes.do?extendedSearch=false&rfn=&searchQuick=Suchen&idc=\')),'
                                                        . '\'ILX\',\'https://www.iloxx.de/net/popup/trackpop.aspx?id=\'),'
                                                        . '\'UPS\',\'http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=de_DE&tracknum=\'),'
                                                        . '\'DPD\',\'http://extranet.dpd.de/cgi-bin/delistrack?typ=1&lang=de&pknr=\'),'
                                                        . '\'GLS\',\'http://www.gls-group.eu/276-I-PORTAL-WEB/content/GLS/DE03/DE/5004.htm?txtAction=71000&txtRefNo=\'),'
                                                        . '\'HMS\',\'http://tracking.hlg.de/Tracking.jsp?TrackID=\'),'
                                                        . '\'" style="text-decoration:underline;" target="_blank">\', o.oxtrackcode, \'</a>\'), '
                                                        . 'DATE(o.oxsenddate) '
                                                . '), '
                                            . '\'-\' '
                                            . ') AS sendstate, '
                                            . 'IF(o.oxremark!=\'\', '
                                                . 'IF((SELECT o.oxremark LIKE \''.$this->IgnoreRemark[$this->SiteID].'\') != 1,'
                                                    . 'CONCAT(\'<img SRC="plugins/OxidPlugin/images/remarks.png" ALT="\', o.oxremark, \'" TITLE="\', o.oxremark, \'" />\'), '
                                                    . '\'\''
                                                . '), '
                                                . '\'\''
                                            . ') AS remark '
					 . 'FROM oxorder o, oxorderarticles a, oxpayments p ' 
					 . 'WHERE  o.oxid = a.oxorderid  '
                                            . 'AND o.oxpaymenttype = p.oxid '
                                            . 'AND DATE(o.oxorderdate) >= \'' . $dateStart . '\' ' 
                                            . 'AND DATE(o.oxorderdate) <= \'' . $dateEnd . '\' '
                                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                            . 'AND o.oxstorno = @STORNOVAL@ '
                                            . 'AND a.oxstorno = @STORNOVAL@ '
					 . 'GROUP BY o.oxordernr '
					 . 'ORDER BY o.oxordernr '; 
				
				$margin = '(SUM(a.oxnetprice) - '
                                            . '(SELECT SUM('
                                                . 'IF(a1.oxparentid=\'\', '
                                                    . 'a1.oxbprice, '
                                                    . 'IF(a1.oxbprice=0.0, (SELECT b2.oxbprice FROM oxarticles b2 WHERE b2.oxid = a1.oxparentid), a1.oxbprice))'
                                                . '*d1.oxamount) '
                                                . 'FROM oxarticles a1, oxorderarticles d1 '
                                                . 'WHERE d1.oxartid = a1.oxid '
                                                    . 'AND o.oxid = d1.oxorderid '
                                                    . 'AND d1.oxstorno = 0)) '
                                            . 'AS netmargin, ';
                                break;
				
				case 'week':
				// daily revenue
				$sql = 'SELECT '
					 . 'CONCAT(CAST(YEAR(o.oxorderdate) AS CHAR(4)), \'-\', CAST(week(o.oxorderdate,3) AS CHAR(2))) AS dateval, '
					 . 'FORMAT(AVG(o.oxtotalordersum),2) AS average, '
					 . 'SUM(o.oxtotalordersum) AS @SUMVAL@, '
                                        
                                         . '@MARGIN@'
                                        
					 . 'COUNT(*) AS count '
					 . 'FROM oxorder o '
					 . 'WHERE '
                                            . 'DATE(o.oxorderdate) >= DATE_SUB(\'' . $dateStart . '\', INTERVAL 30 WEEK) ' 
                                            . 'AND DATE(o.oxorderdate) <= \'' . $dateEnd . '\' '
                                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                            . 'AND o.oxstorno = @STORNOVAL@ '
					 . 'GROUP BY '
                                            . 'dateval '; 
                                            
				$margin = '(SELECT '
                                            . 'SUM(ROUND(d3.oxnetprice,2)) '
                                         . 'FROM '
                                            . 'oxorderarticles d3, oxorder o3 '
                                         . 'WHERE '
                                            . 'o3.oxid = d3.oxorderid '
                                            . 'AND YEAR(o3.oxorderdate) = YEAR(o.oxorderdate) AND WEEK(o3.oxorderdate,3) = WEEK(o.oxorderdate,3) '
                                            . 'AND d3.oxstorno = 0) '
                                         . ' - '
                                         . '(SELECT '
                                            . 'SUM(ROUND(IF('
                                                . 'a1.oxparentid=\'\', '
                                                . 'a1.oxbprice, '
                                                . 'IF('
                                                    . 'a1.oxbprice=0.0, '
                                                    . '(SELECT b2.oxbprice FROM oxarticles b2 WHERE b2.oxid = a1.oxparentid), '
                                                    . 'a1.oxbprice)),2) '
                                                . '*d1.oxamount) '
                                            . 'FROM '
                                                . 'oxarticles a1, oxorderarticles d1, oxorder o1 '
                                            . 'WHERE '
                                                . 'd1.oxartid = a1.oxid '
                                                . 'AND d1.oxorderid = o1.oxid '
                                                . 'AND YEAR(o1.oxorderdate) = YEAR(o.oxorderdate) AND WEEK(o1.oxorderdate,3) = WEEK(o.oxorderdate,3) '
                                                . 'AND d1.oxstorno = 0) '
                                         . 'AS netmargin, ';
                                break;

			case 'month':
				 // weekly revenue
				$sql = 'SELECT '
					 . 'CONCAT(CAST(YEAR(o.oxorderdate) AS CHAR(4)), \'-\', CAST(MONTH(o.oxorderdate) AS CHAR(2))) as dateval, '
					 . 'FORMAT(AVG(o.oxtotalordersum),2) AS average, '
					 . 'SUM(o.oxtotalordersum) AS @SUMVAL@, '
                                        
                                         . '@MARGIN@'

                                         . 'COUNT(*) AS count '
					 . 'FROM oxorder  o '
					 . 'WHERE '
                                            . 'DATE(o.oxorderdate) >= DATE_SUB(\'' . $dateStart . '\', INTERVAL 30 MONTH) ' 
                                            . 'AND DATE(o.oxorderdate) <= \'' . $dateEnd . '\' '
                                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                            . 'AND o.oxstorno = @STORNOVAL@ '
					 . 'GROUP BY '
                                            . 'dateval '; 
                                            
				$margin = '(SELECT '
                                            . 'SUM(ROUND(d3.oxnetprice,2)) '
                                         . 'FROM '
                                            . 'oxorderarticles d3, oxorder o3 '
                                         . 'WHERE '
                                            . 'o3.oxid = d3.oxorderid '
                                            . 'AND YEAR(o3.oxorderdate) = YEAR(o.oxorderdate) AND MONTH(o3.oxorderdate) = MONTH(o.oxorderdate) '
                                            . 'AND d3.oxstorno = 0) '
                                         . ' - '
                                         . '(SELECT '
                                            . 'SUM(ROUND(IF('
                                                . 'a1.oxparentid=\'\', '
                                                . 'a1.oxbprice, '
                                                . 'IF('
                                                    . 'a1.oxbprice=0.0, '
                                                    . '(SELECT b2.oxbprice FROM oxarticles b2 WHERE b2.oxid = a1.oxparentid), '
                                                    . 'a1.oxbprice)),2) '
                                                . '*d1.oxamount) '
                                            . 'FROM '
                                                . 'oxarticles a1, oxorderarticles d1, oxorder o1 '
                                            . 'WHERE '
                                                . 'd1.oxartid = a1.oxid '
                                                . 'AND d1.oxorderid = o1.oxid '
                                                . 'AND YEAR(o1.oxorderdate) = YEAR(o.oxorderdate) AND MONTH(o1.oxorderdate) = MONTH(o.oxorderdate) '
                                                . 'AND d1.oxstorno = 0) '
                                         . 'AS netmargin, ';
                                break;
			 
			case 'year':
				 // monthly revenue
				$sql = 'SELECT extract(year FROM o.oxorderdate) AS dateval, '
					 . 'AVG(o.oxtotalordersum) AS average, '
					 . 'SUM(o.oxtotalordersum) AS @SUMVAL@, '
                                        
                                         . '@MARGIN@'

					 . 'COUNT(*) AS count '
					 . 'FROM oxorder o '
					 . 'WHERE '
                                            . 'DATE(o.oxorderdate) >= DATE_SUB(\'' . $dateStart . '\', INTERVAL 30 YEAR) ' 
                                            . 'AND DATE(o.oxorderdate) <= \'' . $dateEnd . '\' '
                                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                            . 'AND o.oxstorno = @STORNOVAL@ '
					 . 'GROUP BY '
                                            . 'extract(year FROM o.oxorderdate) '; 
                                            
				$margin = '(SELECT '
                                            . 'SUM(ROUND(d3.oxnetprice,2)) '
                                         . 'FROM '
                                            . 'oxorderarticles d3, oxorder o3 '
                                         . 'WHERE '
                                            . 'o3.oxid = d3.oxorderid '
                                            . 'AND YEAR(o3.oxorderdate) = dateval '
                                            . 'AND d3.oxstorno = 0) '
                                         . ' - '
                                         . '(SELECT '
                                            . 'SUM(ROUND(IF('
                                                . 'a1.oxparentid=\'\', '
                                                . 'a1.oxbprice, '
                                                . 'IF('
                                                    . 'a1.oxbprice=0.0, '
                                                    . '(SELECT b2.oxbprice FROM oxarticles b2 WHERE b2.oxid = a1.oxparentid), '
                                                    . 'a1.oxbprice)),2) '
                                                . '*d1.oxamount) '
                                            . 'FROM '
                                                . 'oxarticles a1, oxorderarticles d1, oxorder o1 '
                                            . 'WHERE '
                                                . 'd1.oxartid = a1.oxid '
                                                . 'AND d1.oxorderid = o1.oxid '
                                                . 'AND YEAR(o1.oxorderdate) = dateval '
                                                . 'AND d1.oxstorno = 0) '
                                         . 'AS netmargin, ';
				break;
			 
			 
			case 'old-year':
			 	// yearly revenue
				$sql = 'SELECT extract(year FROM oxorderdate) AS dateval, '
					 . 'AVG(oxtotalordersum) AS average, '
					 . 'SUM(oxtotalordersum) AS @SUMVAL@, '
					 . 'COUNT(*) AS count '
					 . 'FROM oxorder '
					 . 'WHERE '
                                            . 'DATE(oxorderdate) >= \'' . $dateStart . '\' ' 
                                            . 'AND DATE(oxorderdate) <= \'' . $dateEnd . '\' '
                                            . 'AND oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
					 . 'GROUP BY '
					 . 'extract(year FROM oxorderdate) '; 
				 break;
			 
		}
		
		$sql1 = str_replace('@STORNOVAL@', '0', $sql);
		$sql1 = str_replace('@SUMVAL@', 'revsum', $sql1);
		$sql1 = str_replace('@MARGIN@', $margin, $sql1);
		if ($this->DebugMode) logfile('debug', 'getRevenue: '.$sql1);
		$stmt = $db->prepare($sql1);
		$stmt->execute();
		$dbData1 = $stmt->fetchAll();
                if (($this->DebugMode) && (is_bool($dbData1)))
			logfile('error', 'getRevenue: [ERROR] '.$sql1);
		
		$sql2 = str_replace('@STORNOVAL@', '1', $sql);
		$sql2 = str_replace('@SUMVAL@', 'stornosum', $sql2);
		$sql2 = str_replace('@MARGIN@', '\'-\' AS netmargin, ', $sql2);

		$stmt = $db->prepare($sql2);
		$stmt->execute();
		$dbData2 = $stmt->fetchAll();
		if (is_bool($dbData2))
			logfile('error', 'getRevenue: [ERROR] '.$sql2);
		
		$db = null;
		
		$sumRevenue = 0.0;
		$sumMargin = 0.0;
		$i = 0;
		foreach($dbData1 as $value) {
                    $sumRevenue += $value['revsum'];
                    $sumMargin += $value['netmargin'];
                    $dbData1[$i]['count'] = intFormat($dbData1[$i]['count'], $this);
                    if (($period != 'day')&&($period != 'range')) 
                            $dbData1[$i]['average'] = currFormat($dbData1[$i]['average'], $this);
                    $dbData1[$i]['revsum'] = currFormat($dbData1[$i]['revsum'], $this);
                    $dbData1[$i]['netmargin'] = currFormat($dbData1[$i]['netmargin'], $this);
                    if (($period == 'day') || ($period == 'range')) {
                        if (strpos($dbData1[$i]['sendstate'], 'http') === FALSE) {
                            $newURL = 'href="' . $this->CarrierTrackingUrl[$this->SiteID] ;
                            $dbData1[$i]['sendstate'] = str_replace('href="', $newURL, $dbData1[$i]['sendstate']);
                        }
                    }
                    $i++;
		}
		$sumStorno = 0.0;
		$i = 0;
		foreach($dbData2 as $value) {
                    $dbData2[$i]['dateval'] = $dbData2[$i]['dateval'] . ' ';    // just for sorting
                    $sumStorno += $value['stornosum'];
                    $dbData2[$i]['count'] = intFormat($dbData2[$i]['count'], $this);
                    if (($period != 'day')&&($period != 'range')) 
                            $dbData2[$i]['average'] = currFormat($dbData2[$i]['average'], $this);
                    $dbData2[$i]['stornosum'] = currFormat($dbData2[$i]['stornosum'], $this);
                    $i++;
		}
		$this->Style = 'font-weight:bold;';
		if ($sumRevenue == 0.0) {
			if ($sumStorno == 0.0) 
				$txtStorno = currFormat($sumStorno, $this);
			else
				$txtStorno = currFormat($sumStorno, $this). ' (100.0%)';
		} else {
			$txtStorno = currFormat($sumStorno, $this);
			$this->Style = '';
			$txtStorno .= percFormat($sumStorno/$sumRevenue*100.0, $this);
		}
		
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($dbData1);
		$dataTable->addRowsFromArrayWithIndexLabel($dbData2);
		$this->Style = 'font-weight:bold;';
		$dataTable->addRow( 
			new Piwik_DataTable_Row(array( Piwik_DataTable_Row::COLUMNS => array(
				'dateval'=>' ', 
				'count'=>' ', 
				'average' => '<div style="text-align:right;font-weight:bold;">'.Piwik_Translate('OxidPlugin_Sum').'</div>',  
				'revsum' => currFormat($sumRevenue, $this), 
				'netmargin' => currFormat($sumMargin, $this), 
				'stornosum' => $txtStorno ) 
			)));
		return $dataTable;
	}
	
	
	// Retrieve revenue values for graph
	public function getRevenueEvolution($date, $period)
	{

		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$timePeriod = new Piwik_Period_Range($period, 'last30');
			$dateStart = $timePeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $timePeriod->getDateEnd()->toString('Y-m-d');
		}
		
		if ($this->DebugMode) logfile('debug', 'period='.$period);
		switch ($period) {

			case 'day':
			case 'range':
				// daily revenue
				$sql = 'SELECT DATE(oxorderdate) AS label, '
                                        . 'SUM(oxtotalordersum) AS revenue '
                                     . 'FROM oxorder '
                                     . 'WHERE DATE(oxorderdate) >= \'' . $dateStart . '\' ' 
                                        . 'AND DATE(oxorderdate) <= \'' . $dateEnd . '\' '
                                        . 'AND oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                        . 'AND oxstorno = @STORNOVAL@ '
                                     . 'GROUP BY '  
                                        . 'extract(year FROM oxorderdate), ' 
                                        . 'extract(month FROM oxorderdate), '
                                        . 'extract(day FROM oxorderdate) '; 
				break;

			case 'week':
				// weekly revenue
				$sql = 'SELECT '
                                        . 'DATE_FORMAT(oxorderdate, \'%Y-%v\') AS label, '
                                        . 'SUM(oxtotalordersum) AS @SUMVAL@ '
                                     . 'FROM oxorder '
                                     . 'WHERE DATE(oxorderdate) >= \'' . $dateStart . '\' ' 
                                        . 'AND DATE(oxorderdate) <= \'' . $dateEnd . '\' '
                                        . 'AND oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                        . 'AND oxstorno = @STORNOVAL@ '
                                     . 'GROUP BY '
                                        . 'label ';
                                break;

			case 'month':
				 // monthy revenue
				$sql = 'SELECT '
                                        . 'DATE_FORMAT(oxorderdate, \'%Y-%m\') as label, '
                                        . 'SUM(oxtotalordersum) AS @SUMVAL@ '
                                     . 'FROM oxorder '
                                     . 'WHERE DATE(oxorderdate) >= \'' . $dateStart . '\' ' 
                                        . 'AND DATE(oxorderdate) <= \'' . $dateEnd . '\' '
                                        . 'AND oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                        . 'AND oxstorno = @STORNOVAL@ '
                                     . 'GROUP BY '
                                        . 'label '; 
				break;
			 
			case 'year':
				 // yearly revenue
				$sql = 'SELECT extract(year FROM oxorderdate) AS label, '
                                        . 'SUM(oxtotalordersum) AS @SUMVAL@ '
                                     . 'FROM oxorder '
                                     . 'WHERE DATE(oxorderdate) >= \'' . $dateStart . '\' ' 
                                        . 'AND DATE(oxorderdate) <= \'' . $dateEnd . '\' '
                                        . 'AND oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                        . 'AND oxstorno = @STORNOVAL@ '
                                     . 'GROUP BY '
                                        . 'extract(year FROM oxorderdate) '; 
                                break;
			 
			 
			case 'old-year':
			 	// yearly revenue
				$sql = 'SELECT extract(year FROM oxorderdate) AS label, '
                                        . 'SUM(oxtotalordersum) AS @SUMVAL@ '
                                     . 'FROM oxorder '
                                     . 'WHERE DATE(oxorderdate) >= \'' . $dateStart . '\' ' 
                                        . 'AND DATE(oxorderdate) <= \'' . $dateEnd . '\' '
                                        . 'AND oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                        . 'AND oxstorno = @STORNOVAL@ '
                                     . 'GROUP BY '
                                        . 'extract(year FROM oxorderdate) '; 
				 break;
			 
		}
		
			 
		$sql1 = str_replace('@STORNOVAL@', '0', $sql);
		$sql1 = str_replace('@SUMVAL@', 'revenue', $sql1);
		
		if ($this->DebugMode) logfile('debug', 'getRevenueEvolution: '.$sql1);
		$stmt = $db->prepare($sql1);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);
		
		$dbData = addZeroValues ($dateStart, $dateEnd, $period, 'revenue', $dbData);
                if ($this->DebugMode) logfile('debug', 'getRevenueEvolution/dbData---------------------------');
		if ($this->DebugMode) logfile('debug', $dbData);
                if ($this->DebugMode) logfile('debug', 'getRevenueEvolution/dbData---------------------------');
		
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromSimpleArray($dbData);
		if ($this->DebugMode) logfile('debug', $dataTable);
		if ($this->DebugMode) logfile('debug', 'getRevenueEvolution: End-----------------------------');
		
		return $dataTable;
	}
	
	
	// Retrieving the yet not paid CIA 
	public function getReadyToSend($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);
                
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
						
		$sql = 'SELECT o.oxordernr AS orderno, o.oxtotalordersum AS ordersum, o.oxbillsal AS salutation, '
			 . 'CONCAT(\'<nobr>\', o.oxbillcompany, \'</nobr>\') AS company, '
			 . 'CONCAT(\'<a href="mailto:\', o.oxbillemail, \'" style="text-decoration:underline;"><nobr>\', o.oxbillfname, \'&nbsp;\', o.oxbilllname, \'</nobr></a>\') AS name, '
                         . 'IF (o.oxdelcity = \'\', '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxbillstreet,\'+\',o.oxbillstreetnr,\',+\',o.oxbillzip,\'+\',o.oxbillcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxbillzip, \'&nbsp;\', o.oxbillcity, \'</a>\'), '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxdelstreet,\'+\',o.oxdelstreetnr,\',+\',o.oxdelzip,\'+\',o.oxdelcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxdelzip, \'&nbsp;\', o.oxdelcity, \'</a>\') '
                            . ') AS  custdeladdr, '
			 . 'p.oxdesc AS paytype, '
			 . 'GROUP_CONCAT(CONCAT(\'<nobr>\', a.oxamount, \' x \', a.oxtitle, IF (a.oxselvariant != \'\', CONCAT(\' &ndash; \', a.oxselvariant), \'\'), \'</nobr>\') SEPARATOR \'<br>\') AS orderlist, '
			 . '(TO_DAYS(NOW())-TO_DAYS(o.oxorderdate)) AS days, DATE(o.oxorderdate) AS orderdate , '
                         . 'IF(o.oxremark!=\'\', '
                            . 'IF((SELECT o.oxremark LIKE \''.$this->IgnoreRemark[$this->SiteID].'\') != 1,'
                                . 'CONCAT(\'<img SRC="plugins/OxidPlugin/images/remarks.png" ALT="\', o.oxremark, \'" TITLE="\', o.oxremark, \'" />\'), '
                                . '\'\''
                            . '), '
                            . '\'\''
                         . ') AS remark '
                     . 'FROM oxorder o, oxpayments p, oxorderarticles a '
                     . 'WHERE o.oxpaymenttype = p.oxid '
			 . 'AND o.oxid = a.oxorderid  '
			 . 'AND ((o.oxpaid != \'0000-00-00 00:00:00\') OR (o.oxpaymenttype IN ('.$this->PaymentLater[$this->SiteID].'))) '
			 . 'AND o.oxsenddate = \'0000-00-00 00:00:00\' '
			 . 'AND DATE(o.oxorderdate) >= \'' . $dateStart . '\' '
 			 . 'AND DATE(o.oxorderdate)  <= \'' . $dateEnd . '\' '
			 . 'AND o.oxstorno = 0 '
                         . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                     . 'GROUP BY o.oxordernr '
                     . 'ORDER BY days ASC '; 
 			 
		if ($this->DebugMode) logfile('debug', 'getReadyToSend: '.$sql);
 		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		/*echo '<pre>';
		print_r($dbData);
		echo '</pre>';*/
		
		$db = null;
		
		$sumCIA = 0.0;
		$i = 0;
		foreach($dbData as $value) {
			$sumCIA += $value['ordersum'];
			$dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
			$i++;
		}
		if ($this->DebugMode) logfile('debug', $dbData);
                
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		$this->Style = 'font-weight:bold;';
		$dataTable->addRow( 
			new Piwik_DataTable_Row(array( Piwik_DataTable_Row::COLUMNS => array(
				'days' => ' ', 
				'orderdate' => ' ', 
				'orderno' => ' ', 
				'company' => ' ', 
				'name' => ' ', 
				'email' => ' ', 
				'city' => ' ',
				'orderlist' => '<div style="text-align:right;font-weight:bold;">'.Piwik_Translate('OxidPlugin_Sum').'</div>', 
				'ordersum' => currFormat($sumCIA, $this), 
				'remark' => ' ' 
			))));
		return $dataTable;
	}
        
        
        
        //
        public function getTop5DelivererRevenueGraph()
        {
            include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';

            if ($this->DebugMode) logfile('debug', 'getTop5DelivererRevenueGraph(): WELCOME');
            $this->SiteID = Piwik_Common::getRequestVar('idSite');
            $db = openDB($this);

            $date = Piwik_Common::getRequestVar('date');
            $period = Piwik_Common::getRequestVar('period');
            $this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
            if ($this->DebugMode) logfile('debug', 'getTop5DelivererRevenueGraph(): date='.$date);
            if ($this->DebugMode) logfile('debug', 'getTop5DelivererRevenueGraph(): period='.$period);
            
            if ($period == 'range') {
                $dateStart = substr($date, 0, strpos($date, ',')); 
                $dateEnd = substr($date, strpos($date, ',')+1);  
            } else {
                $timePeriod = new Piwik_Period_Range($period, 'last3');
                $dateStart = $timePeriod->getDateStart()->toString('Y-m-d'); 
                $dateEnd = $timePeriod->getDateEnd()->toString('Y-m-d');
            }
            if ($this->DebugMode) logfile('debug', 'getTop5DelivererRevenueGraph: AFTER PERIOD  ');
            
            // retrieve the top5 deliverer
            $sql = "SELECT m.oxid, m.oxtitle, SUM(d.oxamount) AS artcount, SUM(d.oxbrutprice) AS sumprice "
                 . "FROM oxorderarticles d, oxarticles a, oxmanufacturers m, oxorder o "
                 . "WHERE d.oxartid = a.oxid "
                        . "AND IF(a.oxmanufacturerid!='', "
                            . "a.oxmanufacturerid, "
                            . "(SELECT a2.oxmanufacturerid FROM oxarticles a2 WHERE a.oxparentid=a2.oxid)) "
                            . "= m.oxid "
                        . "AND d.oxstorno = 0 "
                        . "AND d.oxorderid = o.oxid "
                        . "AND DATE(o.oxorderdate) >= '{$dateStart}' " 
                        . "AND DATE(o.oxorderdate) <= '{$dateEnd}' "
                 . "GROUP BY m.oxtitle "
                 . "ORDER BY SUM(d.oxbrutprice) DESC "
                 . "LIMIT 0, 5 ";

            if ($this->DebugMode) logfile('debug', 'getTop5DelivererRevenueGraph: '.$sql);
            $stmt = $db->prepare($sql);
            $stmt->execute();
            $dbData = $stmt->fetchAll();
            if ($this->DebugMode) logfile('debug', $dbData);
            
            if ($period == 'range') {
                $dateStart = substr($date, 0, strpos($date, ',')); 
                $dateEnd = substr($date, strpos($date, ',')+1);  
            } else {
                $timePeriod = new Piwik_Period_Range($period, 'last30');
                $dateStart = $timePeriod->getDateStart()->toString('Y-m-d'); 
                $dateEnd = $timePeriod->getDateEnd()->toString('Y-m-d');
            }

            $i = 0;
            $this->ColumnLabels = '';
            $columnLabel = '';
            foreach($dbData as $value) {
                $columnLabel .= $dbData[$i]['oxtitle'] . '|';
                switch ($period) {
                    case 'day':
                    case 'range':
                        $sql = "SELECT DATE(o.oxorderdate) AS label, SUM(d.oxbrutprice) AS revenue "
                            . "FROM oxorderarticles d, oxarticles a, oxmanufacturers m, oxorder o "
                            . "WHERE d.oxartid = a.oxid "
                               . "AND IF(a.oxmanufacturerid!='', "
                                   . "a.oxmanufacturerid, "
                                   . "(SELECT a2.oxmanufacturerid FROM oxarticles a2 WHERE a.oxparentid=a2.oxid)) "
                                   . "= m.oxid "
                               . "AND d.oxstorno = 0 "
                               . "AND d.oxorderid = o.oxid "
                               . "AND DATE(o.oxorderdate) >= '{$dateStart}' " 
                               . "AND DATE(o.oxorderdate) <= '{$dateEnd}' "
                               . "AND m.oxid = '{$dbData[$i]['oxid']}' "
                            . "GROUP BY DATE(o.oxorderdate) "
                            . "ORDER BY DATE(o.oxorderdate) ASC";
                        break;

                    case 'week':
                        $sql = "SELECT DATE_FORMAT(oxorderdate, '%Y-%v') AS label, SUM(d.oxbrutprice) AS revenue "
                            . "FROM oxorderarticles d, oxarticles a, oxmanufacturers m, oxorder o "
                            . "WHERE d.oxartid = a.oxid "
                               . "AND IF(a.oxmanufacturerid!='', "
                                   . "a.oxmanufacturerid, "
                                   . "(SELECT a2.oxmanufacturerid FROM oxarticles a2 WHERE a.oxparentid=a2.oxid)) "
                                   . "= m.oxid "
                               . "AND d.oxstorno = 0 "
                               . "AND d.oxorderid = o.oxid "
                               . "AND DATE(o.oxorderdate) >= '{$dateStart}' " 
                               . "AND DATE(o.oxorderdate) <= '{$dateEnd}' "
                               . "AND m.oxid = '{$dbData[$i]['oxid']}' "
                            . "GROUP BY DATE_FORMAT(oxorderdate, '%Y-%v') "
                            . "ORDER BY DATE_FORMAT(oxorderdate, '%Y-%v') ASC";
                        break;

                    case 'month':
                        $sql = "SELECT DATE_FORMAT(oxorderdate, '%Y-%m') AS label, SUM(d.oxbrutprice) AS revenue "
                            . "FROM oxorderarticles d, oxarticles a, oxmanufacturers m, oxorder o "
                            . "WHERE d.oxartid = a.oxid "
                               . "AND IF(a.oxmanufacturerid!='', "
                                   . "a.oxmanufacturerid, "
                                   . "(SELECT a2.oxmanufacturerid FROM oxarticles a2 WHERE a.oxparentid=a2.oxid)) "
                                   . "= m.oxid "
                               . "AND d.oxstorno = 0 "
                               . "AND d.oxorderid = o.oxid "
                               . "AND DATE(o.oxorderdate) >= '{$dateStart}' " 
                               . "AND DATE(o.oxorderdate) <= '{$dateEnd}' "
                               . "AND m.oxid = '{$dbData[$i]['oxid']}' "
                            . "GROUP BY YEAR(o.oxorderdate), MONTH(o.oxorderdate) "
                            . "ORDER BY YEAR(o.oxorderdate), MONTH(o.oxorderdate) ASC";
                        break;

                    case 'year':
                        $sql = "SELECT YEAR(o.oxorderdate) AS label, SUM(d.oxbrutprice) AS revenue "
                            . "FROM oxorderarticles d, oxarticles a, oxmanufacturers m, oxorder o "
                            . "WHERE d.oxartid = a.oxid "
                               . "AND IF(a.oxmanufacturerid!='', "
                                   . "a.oxmanufacturerid, "
                                   . "(SELECT a2.oxmanufacturerid FROM oxarticles a2 WHERE a.oxparentid=a2.oxid)) "
                                   . "= m.oxid "
                               . "AND d.oxstorno = 0 "
                               . "AND d.oxorderid = o.oxid "
                               . "AND DATE(o.oxorderdate) >= '{$dateStart}' " 
                               . "AND DATE(o.oxorderdate) <= '{$dateEnd}' "
                               . "AND m.oxid = '{$dbData[$i]['oxid']}' "
                            . "GROUP BY YEAR(o.oxorderdate) "
                            . "ORDER BY YEAR(o.oxorderdate) ASC";
                        break;
                }
		
                if ($this->DebugMode) logfile('debug', 'getTop5DelivererRevenueGraph: '.$sql);
                $stmt = $db->prepare($sql);
		$stmt->execute();
		$dbRevData = $stmt->fetchAll(PDO::FETCH_NAMED);
                if ($this->DebugMode) logfile('debug', $dbRevData);
                
                $dbRevData = addZeroValues ($dateStart, $dateEnd, $period, 'revenue', $dbRevData);
                if ($this->DebugMode) logfile('debug', count($dbRevData));
                for ($j=0; $j<count($dbRevData); $j++) {
                    $graphData[$dbRevData[$j]['label']]["revenue{$i}"] = $dbRevData[$j]['revenue'];
                }
                if ($this->DebugMode) logfile('debug', '$graphData');
                if ($this->DebugMode) logfile('debug', $graphData);
		
                $i++;
            }

            $db = null;
            if ($this->DebugMode) logfile('debug', $columnLabel);
            /*$this->ColumnLabels .= '-';
            if ($this->DebugMode) logfile('debug', $columnLabel);
            $ColumnLabel = explode('|', $this->ColumnLabels);
            if ($this->DebugMode) logfile('debug', ColumnLabel);*/

            $dataTable = new Piwik_DataTable();
            // convert this array to a DataTable object
            //old// $dataTable->addRowsFromSimpleArray($dbRevData);
            $dataTable->addRowsFromArrayWithIndexLabel($graphData);

            return $dataTable;

        }
        
        
        //
        public function getRevenueAlerts()
        {
            include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';

            if ($this->DebugMode) logfile('debug', 'getRevenueAlerts(): WELCOME');
            $this->SiteID = Piwik_Common::getRequestVar('idSite');
            $db = openDB($this);

            $date = Piwik_Common::getRequestVar('date');
            $period = Piwik_Common::getRequestVar('period');
            $this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
            if ($this->DebugMode) logfile('debug', 'getRevenueAlerts(): date='.$date);
            if ($this->DebugMode) logfile('debug', 'getRevenueAlerts(): period='.$period);
            
            $period = 'day';
            $timePeriod = new Piwik_Period_Range($period, 'last180');
            $dateStart = $timePeriod->getDateStart()->toString('Y-m-d'); 
            $dateEnd = $timePeriod->getDateEnd()->toString('Y-m-d');
            
            // 
            $sql = "SELECT m.oxid, m.oxtitle, SUM(d.oxamount) AS artcount, SUM(d.oxbrutprice) AS sumprice "
                 . "FROM oxorderarticles d, oxarticles a, oxmanufacturers m, oxorder o "
                 . "WHERE d.oxartid = a.oxid "
                        . "AND IF(a.oxmanufacturerid!='', "
                            . "a.oxmanufacturerid, "
                            . "(SELECT a2.oxmanufacturerid FROM oxarticles a2 WHERE a.oxparentid=a2.oxid)) "
                            . "= m.oxid "
                        . "AND d.oxstorno = 0 "
                        . "AND d.oxorderid = o.oxid "
                        . "AND DATE(o.oxorderdate) >= '{$dateStart}' " 
                        . "AND DATE(o.oxorderdate) <= '{$dateEnd}' "
                 . "GROUP BY m.oxtitle "
                 . "ORDER BY m.oxtitle DESC ";

            if ($this->DebugMode) logfile('debug', 'getRevenueAlerts-Longterm: '.$sql);
            $stmt = $db->prepare($sql);
            $stmt->execute();
            $dbDataLongterm = $stmt->fetchAll(PDO::FETCH_NAMED);
            if ($this->DebugMode) logfile('debug', $dbDataLongterm);
            
            $period = 'day';
            $timePeriod = new Piwik_Period_Range($period, 'last30');
            $dateStart = $timePeriod->getDateStart()->toString('Y-m-d'); 
            $dateEnd = $timePeriod->getDateEnd()->toString('Y-m-d');
            
            $sql = "SELECT m.oxid, m.oxtitle, SUM(d.oxamount) AS artcount, SUM(d.oxbrutprice) AS sumprice "
                 . "FROM oxorderarticles d, oxarticles a, oxmanufacturers m, oxorder o "
                 . "WHERE d.oxartid = a.oxid "
                        . "AND IF(a.oxmanufacturerid!='', "
                            . "a.oxmanufacturerid, "
                            . "(SELECT a2.oxmanufacturerid FROM oxarticles a2 WHERE a.oxparentid=a2.oxid)) "
                            . "= m.oxid "
                        . "AND d.oxstorno = 0 "
                        . "AND d.oxorderid = o.oxid "
                        . "AND DATE(o.oxorderdate) >= '{$dateStart}' " 
                        . "AND DATE(o.oxorderdate) <= '{$dateEnd}' "
                 . "GROUP BY m.oxtitle "
                 . "ORDER BY m.oxtitle DESC ";

            if ($this->DebugMode) logfile('debug', 'getRevenueAlerts-Shortterm: '.$sql);
            $stmt = $db->prepare($sql);
            $stmt->execute();
            $dbDataShortterm = $stmt->fetchAll(PDO::FETCH_NAMED);
            if ($this->DebugMode) logfile('debug', $dbDataShortterm);
            
            $i = 0;
            $dbData = array();
            foreach ($dbDataLongterm as $longvalue) {
                $dbData[$i]['title'] = $dbDataLongterm[$i]['oxtitle'];
                $dbData[$i]['longrev'] = $dbDataLongterm[$i]['sumprice'];
                $dbData[$i]['shortrev'] = 0.0;
                $dbData[$i]['missed'] = -($dbData[$i]['longrev'] / 180.0*30.0);
                //$dbData[$i]['missedtext'] = '-';
                $dbData[$i]['evolution'] = -100.0;
                //$dbData[$i]['evolutiontext'] = '-100.0 %';
                foreach ($dbDataShortterm as $shortvalue) {
                    if ($shortvalue['oxid'] == $longvalue['oxid']) {
                        $dbData[$i]['shortrev'] = $shortvalue['sumprice'];
                        $dbData[$i]['missed'] = $dbData[$i]['shortrev'] -($dbData[$i]['longrev'] / 180.0*30.0);
                        $dbData[$i]['evolution'] = ($dbData[$i]['shortrev'] / 30.0) / ($dbData[$i]['longrev'] / 180.0) * 100.0 - 100.0;
                    }
                }
                $dbData[$i]['longrev'] = currFormat($dbData[$i]['longrev'], $this);
                $dbData[$i]['shortrev'] = currFormat($dbData[$i]['shortrev'], $this);
                $dbData[$i]['missedtext'] = currFormat($dbData[$i]['missed'], $this);
                $dbData[$i]['evolutiontext'] = percFormat($dbData[$i]['evolution'], $this);
                $i++;
            }
            if ($this->DebugMode) logfile('debug', $dbData);
            
            $dataTable = new Piwik_DataTable();
            // convert this array to a DataTable object
            $dataTable->addRowsFromSimpleArray($dbData);
            
            return $dataTable;
        }
	
	
	// Retrieving the delivered orders without feedback reminder
	public function getFeedbackList($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
		
		$sql = 'SELECT oxid, oxordernr AS orderno, oxbillsal AS salutation, oxbillcompany AS company, '
			 . 'CONCAT(oxbillfname, \'&nbsp;\', oxbilllname) AS name, '
			 . 'CONCAT(\'<a href="mailto:\', oxbillemail, \'" style="text-decoration:underline;">\', oxbillemail, \'</a>\') AS email, '
			 . 'CONCAT(oxbillzip, \'&nbsp;\', oxbillcity) AS city, '
			 . 'CONCAT(\'<a href="\','
				. 'REPLACE('
					. 'REPLACE('
						. 'REPLACE('
                					. 'REPLACE('
                        					. 'REPLACE('
                                					. 'REPLACE(oxtrackcode,'
                                        					. '\'DHL\',CONCAT(\'http://nolp.dhl.de/nextt-online-public/set_identcodes.do?extendedSearch=false&rfn=&searchQuick=Suchen&idc=\')),'
        								. '\'ILX\',\'https://www.iloxx.de/net/popup/trackpop.aspx?id=\'),'
								. '\'UPS\',\'http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=de_DE&tracknum=\'),'
							. '\'DPD\',\'http://extranet.dpd.de/cgi-bin/delistrack?typ=1&lang=de&pknr=\'),'
						. '\'GLS\',\'http://www.gls-group.eu/276-I-PORTAL-WEB/content/GLS/DE03/DE/5004.htm?txtAction=71000&txtRefNo=\'),'
					. '\'HMS\',\'http://tracking.hlg.de/Tracking.jsp?TrackID=\'),'
				. '\'" style="text-decoration:underline;" target="_blank">\', oxtrackcode, \'</a>\') '
			 . 'AS trackcode , '
			 . 'DATE(oxorderdate) AS orderdate, (TO_DAYS(oxsenddate)-TO_DAYS(oxorderdate)) AS days, DATE(oxsenddate) AS senddate, oxtotalordersum AS ordersum '
			 . 'FROM oxorder '
			 . 'WHERE oxordersurvey = \'0000-00-00 00:00:00\' '
                            . 'AND DATE(oxsenddate) >= \'' . $dateStart . '\' '
                            . 'AND DATE(oxsenddate)  <= \'' . $dateEnd . '\' '
                            . 'AND oxstorno = 0 '
                            . 'AND oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
 			 . 'ORDER BY days ASC '; 

		if ($this->DebugMode) logfile('debug', 'getFeedbacklist: '.$sql);
 		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();

		$i = 0;
                foreach($dbData as $value) {
                    if (($period == 'day') || ($period == 'range')) {
                        if (strpos($dbData[$i]['trackcode'], 'http') === FALSE) {
                            $newURL = 'href="' . $this->CarrierTrackingUrl[$this->SiteID] ;
                            $dbData[$i]['trackcode'] = str_replace('href="', $newURL, $dbData[$i]['trackcode']);
                        }
                    }
                    $sql = 'SELECT oxamount, oxtitle '
                            . 'FROM oxorderarticles '
                            . 'WHERE oxorderid = \''.$dbData[$i]['oxid'].'\' '
                                . 'AND oxstorno = 0 ';
                    $stmt = $db->prepare($sql);
                    $stmt->execute();
                    $details = $stmt->fetchAll(PDO::FETCH_NAMED);
                    $dbData[$i]['oxdetails'] = Piwik_Translate('OxidPlugin_Order') . ':';
                    foreach ($details as $detail) {
                        $dbData[$i]['oxdetails'] = $dbData[$i]['oxdetails'] . chr(13) . $detail['oxamount'] . ' x ' . $detail['oxtitle'];
                    }
                    $dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
                    $dbData[$i]['ordersum'] = addTitle($dbData[$i]['ordersum'], $dbData[$i]['oxdetails']);
                    $i++;
                }
                
		$db = null;

                // convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		return $dataTable;
	}
	
	
	// Retrieving the yet not paid CIA 
	public function getCIAnotPaid($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
						
		$sql = 'SELECT o.oxordernr AS orderno, o.oxtotalordersum AS ordersum, o.oxbillsal AS salutation, '
			 . 'o.oxbillcompany AS company, '
                         . 'CONCAT(\'<a href="mailto:\', o.oxbillemail, \'" style="text-decoration:underline;">\', o.oxbillfname, \'&nbsp;\', o.oxbilllname, \'</a>\') AS name, '
                         . 'IF (o.oxdelcity = \'\', '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxbillstreet,\'+\',o.oxbillstreetnr,\',+\',o.oxbillzip,\'+\',o.oxbillcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxbillzip, \'&nbsp;\', o.oxbillcity, \'</a>\'), '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxdelstreet,\'+\',o.oxdelstreetnr,\',+\',o.oxdelzip,\'+\',o.oxdelcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxdelzip, \'&nbsp;\', o.oxdelcity, \'</a>\') '
                            . ') AS  custdeladdr, '
			 . '(TO_DAYS(NOW())-TO_DAYS(o.oxorderdate)) AS days, DATE(o.oxorderdate) AS orderdate, '
			 . 'GROUP_CONCAT(CONCAT(\'<nobr>\', a.oxamount, \' x \', a.oxtitle, IF (a.oxselvariant != \'\', CONCAT(\' &ndash; \', a.oxselvariant), \'\'), \'</nobr>\') SEPARATOR \'<br>\') AS orderlist, '
                         . 'IF(o.oxremark!=\'\', '
                            . 'IF((SELECT o.oxremark LIKE \''.$this->IgnoreRemark[$this->SiteID].'\') != 1,'
                                . 'CONCAT(\'<img SRC="plugins/OxidPlugin/images/remarks.png" ALT="\', o.oxremark, \'" TITLE="\', o.oxremark, \'" />\'), '
                                . '\'\''
                            . '), '
                            . '\'\''
                         . ') AS remark '
			 . 'FROM oxorder o, oxorderarticles a '
			 . 'WHERE '
                            . 'o.oxpaymenttype = '.$this->PaymentCIA[$this->SiteID].' '
                            . 'AND o.oxpaid = \'0000-00-00 00:00:00\' '
                            . 'AND DATE(o.oxorderdate) >= \'' . $dateStart . '\' '
                            . 'AND DATE(o.oxorderdate)  <= \'' . $dateEnd . '\' '
                            . 'AND o.oxstorno = 0 '
                            . 'AND o.oxid = a.oxorderid  '
                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
			 . 'GROUP BY o.oxordernr '
 			 . 'ORDER BY days ASC '; 
 			 
		if ($this->DebugMode) logfile('debug', 'getCIAnotPaid: '.$sql);
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		/*echo '<pre>';
		print_r($dbData);
		echo '</pre>';*/
		
		$db = null;
		
		$sumCIA = 0.0;
		$i = 0;
		foreach($dbData as $value) {
			$sumCIA += $value['ordersum'];
			$dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
			$i++;
		}
		
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		$this->Style = 'font-weight:bold;';
		$dataTable->addRow( 
			new Piwik_DataTable_Row(array( Piwik_DataTable_Row::COLUMNS => array(
				'days'=>' ', 
				'orderdate'=>' ', 
				'orderno'=>' ', 
				'company'=>' ', 
				'name'=>' ', 
				'email'=>' ', 
				'city' => '<div style="text-align:right;font-weight:bold;">'.Piwik_Translate('OxidPlugin_Sum').'</div>',
				'ordersum' => currFormat($sumCIA, $this) 
			))));
		return $dataTable;
	}
	
	
	// Retrieving the yet not received the COD 
	public function getCODnotReceived($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
						
		$sql = 'SELECT o.oxordernr AS orderno, o.oxtotalordersum AS ordersum, o.oxbillsal AS salutation, '
			 . 'CONCAT(\'<a href="mailto:\', o.oxbillemail, \'" style="text-decoration:underline;"><nobr>\', o.oxbillfname, \'&nbsp;\', o.oxbilllname, \'</nobr></a>\') AS name, '
                         . 'IF (o.oxdelcity = \'\', '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxbillstreet,\'+\',o.oxbillstreetnr,\',+\',o.oxbillzip,\'+\',o.oxbillcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxbillstreet, \'&nbsp;\', o.oxbillstreetnr, \', \', o.oxbillzip, \'&nbsp;\', o.oxbillcity, \'</a>\'), '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxdelstreet,\'+\',o.oxdelstreetnr,\',+\',o.oxdelzip,\'+\',o.oxdelcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxdelstreet, \'&nbsp;\', o.oxdelstreetnr, \', \', o.oxdelzip, \'&nbsp;\', o.oxdelcity, \'</a>\') '
                            . ') AS  custdeladdr, '
			 . 'CONCAT(\'<a href="\','
				. 'REPLACE('
					. 'REPLACE('
						. 'REPLACE('
                					. 'REPLACE('
                        					. 'REPLACE('
                                					. 'REPLACE(o.oxtrackcode,'
                                        					. '\'DHL\',CONCAT(\'http://nolp.dhl.de/nextt-online-public/set_identcodes.do?extendedSearch=false&rfn=&searchQuick=Suchen&idc=\')),'
        								. '\'ILX\',\'https://www.iloxx.de/net/popup/trackpop.aspx?id=\'),'
								. '\'UPS\',\'http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=de_DE&tracknum=\'),'
							. '\'DPD\',\'http://extranet.dpd.de/cgi-bin/delistrack?typ=1&lang=de&pknr=\'),'
						. '\'GLS\',\'http://www.gls-group.eu/276-I-PORTAL-WEB/content/GLS/DE03/DE/5004.htm?txtAction=71000&txtRefNo=\'),'
					. '\'HMS\',\'http://tracking.hlg.de/Tracking.jsp?TrackID=\'),'
				. '\'" style="text-decoration:underline;" target="_blank">\', oxtrackcode, \'</a>\') '
			 . 'AS trackcode , '
			 . '(TO_DAYS(NOW())-TO_DAYS(o.oxsenddate)) AS days, DATE(o.oxsenddate) AS senddate, '
			 . 'GROUP_CONCAT(CONCAT(\'<nobr>\', a.oxamount, \' x \', a.oxtitle, IF (a.oxselvariant != \'\', CONCAT(\' &ndash; \', a.oxselvariant), \'\'), \'</nobr>\') SEPARATOR \'<br>\') AS orderlist, '
                         . 'IF(o.oxremark!=\'\', '
                            . 'IF((SELECT o.oxremark LIKE \''.$this->IgnoreRemark[$this->SiteID].'\') != 1,'
                                . 'CONCAT(\'<img SRC="plugins/OxidPlugin/images/remarks.png" ALT="\', o.oxremark, \'" TITLE="\', o.oxremark, \'" />\'), '
                                . '\'\''
                            . '), '
                            . '\'\''
                         . ') AS remark '
			 . 'FROM oxorder o, oxorderarticles a '
			 . 'WHERE '
                            . 'o.oxpaymenttype = '.$this->PaymentCOD[$this->SiteID].' '
                            . 'AND o.oxsenddate != \'0000-00-00 00:00:00\' '
                            . 'AND o.oxpaid = \'0000-00-00 00:00:00\' '
                            . 'AND DATE(o.oxorderdate) >= \'' . $dateStart . '\' '
                            . 'AND DATE(o.oxorderdate)  <= \'' . $dateEnd . '\' '
                            . 'AND o.oxstorno = 0 '
                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                            . 'AND o.oxid = a.oxorderid  '
			 . 'GROUP BY o.oxordernr '
 			 . 'ORDER BY days ASC '; 
 			 
		if ($this->DebugMode) logfile('debug', 'getCODnotReceived: '.$sql);
 		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		/*echo '<pre>';
		print_r($dbData);
		echo '</pre>';*/
		
		$db = null;
		
		$sumCOD = 0.0;
		$i = 0;
		foreach($dbData as $value) {
                    $sumCOD += $value['ordersum'];
                    $dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
                    if (($period == 'day') || ($period == 'range')) {
                        if (strpos($dbData[$i]['trackcode'], 'http') === FALSE) {
                            $newURL = 'href="' . $this->CarrierTrackingUrl[$this->SiteID] ;
                            $dbData[$i]['trackcode'] = str_replace('href="', $newURL, $dbData[$i]['trackcode']);
                        }
                    }
                    $i++;
		}
		
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		$this->Style = 'font-weight:bold;';
		$dataTable->addRow( 
			new Piwik_DataTable_Row(array( Piwik_DataTable_Row::COLUMNS => array(
				'days'=>' ', 
				'senddate'=>' ', 
				'orderno'=>' ', 
				'trackcode'=>' ', 
				'company'=>' ', 
				'name'=>' ', 
				'email'=>' ', 
				'city' => '<div style="text-align:right;font-weight:bold;">'.Piwik_Translate('OxidPlugin_Sum').'</div>',
				'ordersum' => currFormat($sumCOD, $this) 
			))));
		return $dataTable;
	}
	
	
	// Retrieving the yet not paid invoices  
	public function getInvoiceNotPaid($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range'; */
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
						
		$sql = 'SELECT o.oxordernr AS orderno, o.oxtotalordersum AS ordersum, o.oxbillsal AS salutation, '
			 . 'CONCAT(\'<nobr>\', o.oxbillcompany, \'</nobr>\') AS company, CONCAT(o.oxbillfname, \'&nbsp;\', o.oxbilllname) AS name, '
			 . 'CONCAT(\'<a href="mailto:\', o.oxbillemail, \'" style="text-decoration:underline;"><nobr>\', o.oxbillfname, \'&nbsp;\', o.oxbilllname, \'</nobr></a>\') AS name, '
                         . 'IF (o.oxdelcity = \'\', '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxbillstreet,\'+\',o.oxbillstreetnr,\',+\',o.oxbillzip,\'+\',o.oxbillcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxbillstreet, \'&nbsp;\', o.oxbillstreetnr, \', \', o.oxbillzip, \'&nbsp;\', o.oxbillcity, \'</a>\'), '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxdelstreet,\'+\',o.oxdelstreetnr,\',+\',o.oxdelzip,\'+\',o.oxdelcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxdelstreet, \'&nbsp;\', o.oxdelstreetnr, \', \', o.oxdelzip, \'&nbsp;\', o.oxdelcity, \'</a>\') '
                            . ') AS  custdeladdr, '
			 . 'CONCAT(\'<a href="\','
				. 'REPLACE('
					. 'REPLACE('
						. 'REPLACE('
                					. 'REPLACE('
                        					. 'REPLACE('
                                					. 'REPLACE(o.oxtrackcode,'
                                        					. '\'DHL\',CONCAT(\'http://nolp.dhl.de/nextt-online-public/set_identcodes.do?extendedSearch=false&rfn=&searchQuick=Suchen&idc=\')),'
        								. '\'ILX\',\'https://www.iloxx.de/net/popup/trackpop.aspx?id=\'),'
								. '\'UPS\',\'http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=de_DE&tracknum=\'),'
							. '\'DPD\',\'http://extranet.dpd.de/cgi-bin/delistrack?typ=1&lang=de&pknr=\'),'
						. '\'GLS\',\'http://www.gls-group.eu/276-I-PORTAL-WEB/content/GLS/DE03/DE/5004.htm?txtAction=71000&txtRefNo=\'),'
					. '\'HMS\',\'http://tracking.hlg.de/Tracking.jsp?TrackID=\'),'
				. '\'" style="text-decoration:underline;" target="_blank">\', oxtrackcode, \'</a>\') '
			 . 'AS trackcode , '
			 . '(TO_DAYS(NOW())-TO_DAYS(o.oxsenddate)) AS days, DATE(o.oxsenddate) AS senddate, '
			 . 'GROUP_CONCAT(CONCAT(\'<nobr>\', a.oxamount, \' x \', a.oxtitle, IF (a.oxselvariant != \'\', CONCAT(\' &ndash; \', a.oxselvariant), \'\'), \'</nobr>\') SEPARATOR \'<br>\') AS orderlist, '
                         . 'IF(o.oxremark!=\'\', '
                            . 'IF((SELECT o.oxremark LIKE \''.$this->IgnoreRemark[$this->SiteID].'\') != 1,'
                                . 'CONCAT(\'<img SRC="plugins/OxidPlugin/images/remarks.png" ALT="\', o.oxremark, \'" TITLE="\', o.oxremark, \'" />\'), '
                                . '\'\''
                            . '), '
                            . '\'\''
                         . ') AS remark '
			 . 'FROM oxorder o, oxorderarticles a '
			 . 'WHERE '
                            . 'oxpaymenttype = '.$this->PaymentInvoice[$this->SiteID].' '
                            . 'AND o.oxsenddate != \'0000-00-00 00:00:00\' '
                            . 'AND o.oxpaid = \'0000-00-00 00:00:00\' '
                            . 'AND DATE(o.oxorderdate) >= \'' . $dateStart . '\' '
                            . 'AND DATE(o.oxorderdate)  <= \'' . $dateEnd . '\' '
                            . 'AND o.oxstorno = 0 '
                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                            . 'AND o.oxid = a.oxorderid  '
			 . 'GROUP BY o.oxordernr '
 			 . 'ORDER BY days ASC '; 
 			 
		if ($this->DebugMode) logfile('debug', 'getInvoiceNotPaid: '.$sql);
 		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		
		$db = null;
		
		$sumInvoices = 0.0;
		$i = 0;
		foreach($dbData as $value) {
                    $sumInvoices += $value['ordersum'];
                    $dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
                    if (($period == 'day') || ($period == 'range')) {
                        if (strpos($dbData[$i]['trackcode'], 'http') === FALSE) {
                            $newURL = 'href="' . $this->CarrierTrackingUrl[$this->SiteID] ;
                            $dbData[$i]['trackcode'] = str_replace('href="', $newURL, $dbData[$i]['trackcode']);
                        }
                    }
                    $i++;
		}
		
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		$this->Style = 'font-weight:bold;';
		$dataTable->addRow( 
			new Piwik_DataTable_Row(array( Piwik_DataTable_Row::COLUMNS => array(
				'days'=>' ', 
				'senddate'=>' ', 
				'orderno'=>' ', 
				'trackcode'=>' ', 
				'company'=>' ', 
				'name'=>' ', 
				'email'=>' ', 
				'city' => '<div style="text-align:right;font-weight:bold;">'.Piwik_Translate('OxidPlugin_Sum').'</div>', 
				'ordersum' => currFormat($sumInvoices, $this) 
			))));
		return $dataTable;
	}
	
	
	// Retrieving the already payed pay in advance orders
	public function getPrePaid($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
						
		$sql = 'SELECT o.oxordernr AS orderno, o.oxtotalordersum AS ordersum, o.oxbillsal AS salutation, '
			 . 'CONCAT(\'<nobr>\', o.oxbillcompany, \'</nobr>\') AS company, '
			 . 'CONCAT(\'<a href="mailto:\', o.oxbillemail, \'" style="text-decoration:underline;"><nobr>\', o.oxbillfname, \'&nbsp;\', o.oxbilllname, \'</nobr></a>\') AS name, '
                         . 'IF (o.oxdelcity = \'\', '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxbillstreet,\'+\',o.oxbillstreetnr,\',+\',o.oxbillzip,\'+\',o.oxbillcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxbillstreet, \'&nbsp;\', o.oxbillstreetnr, \', \', o.oxbillzip, \'&nbsp;\', o.oxbillcity, \'</a>\'), '
                            . 'CONCAT(\'<a href="http://maps.google.com/maps?f=q&hl=de&geocode=&q=\', o.oxdelstreet,\'+\',o.oxdelstreetnr,\',+\',o.oxdelzip,\'+\',o.oxdelcity,\'&z=10" style="text-decoration:underline;" target="_blank">\', o.oxdelstreet, \'&nbsp;\', o.oxdelstreetnr, \', \', o.oxdelzip, \'&nbsp;\', o.oxdelcity, \'</a>\') '
                            . ') AS  custdeladdr, '
			 . 'p.oxdesc AS paytype, '
			 . '(TO_DAYS(NOW())-TO_DAYS(o.oxorderdate)) AS days, DATE(o.oxorderdate) AS orderdate, '
			 . 'GROUP_CONCAT(CONCAT(\'<nobr>\', a.oxamount, \' x \', a.oxtitle, IF (a.oxselvariant != \'\', CONCAT(\' &ndash; \', a.oxselvariant), \'\'), \'</nobr>\') SEPARATOR \'<br>\') AS orderlist, '
                         . 'IF(o.oxremark!=\'\', '
                            . 'IF((SELECT o.oxremark LIKE \''.$this->IgnoreRemark[$this->SiteID].'\') != 1,'
                                . 'CONCAT(\'<img SRC="plugins/OxidPlugin/images/remarks.png" ALT="\', o.oxremark, \'" TITLE="\', o.oxremark, \'" />\'), '
                                . '\'\''
                            . '), '
                            . '\'\''
                         . ') AS remark '
			 . 'FROM oxorder o, oxorderarticles a, oxpayments p '
			 . 'WHERE '
                            . 'o.oxpaymenttype = p.oxid '
                            . 'AND o.oxpaid != \'0000-00-00 00:00:00\' '
                            . 'AND o.oxsenddate = \'0000-00-00 00:00:00\' '
                            . 'AND DATE(o.oxorderdate) >= \'' . $dateStart . '\' '
                            . 'AND DATE(o.oxorderdate)  <= \'' . $dateEnd . '\' '
                            . 'AND o.oxstorno = 0 '
                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                            . 'AND o.oxid = a.oxorderid '
			 . 'GROUP BY o.oxordernr '
 			 . 'ORDER BY days ASC '; 
 			 
		if ($this->DebugMode) logfile('debug', 'getPrePaid: '.$sql);
 		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		/*echo '<pre>';
		print_r($dbData);
		echo '</pre>';*/
		
		$db = null;
		
		$sumPrePaid = 0.0;
		$i = 0;
		foreach($dbData as $value) {
			$sumPrePaid += $value['ordersum'];
			$dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
			$i++;
		}
		
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		$this->Style = 'font-weight:bold;';
		$dataTable->addRow( 
			new Piwik_DataTable_Row(array( Piwik_DataTable_Row::COLUMNS => array(
				'days'=>' ', 
				'orderdate'=>' ', 
				'orderno'=>' ', 
				'company'=>' ', 
				'name'=>' ', 
				'email'=>' ', 
				'city'=>' ', 
				'paytype' => '<div style="text-align:right;font-weight:bold;">'.Piwik_Translate('OxidPlugin_Sum').'</div>',
				'ordersum' => currFormat($sumPrePaid, $this) 
			))));
		return $dataTable;
	}
	
	
	// Retrieving the Top Seller  
	public function getTopSeller($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
						
		$sql = 'SELECT SUM(a.oxamount) AS artcount, a.oxartnum AS artno, CONCAT(\'<nobr>\', a.oxtitle, \'</nobr>\') AS arttitle, SUM(a.oxbrutprice) AS artrev '
                     . 'FROM oxorder o, oxorderarticles a ' 
                     . 'WHERE o.OXID = a.OXORDERID ' 
                        . 'AND DATE(o.oxorderdate) >= \'' . $dateStart . '\' '
                        . 'AND DATE(o.oxorderdate)  <= \'' . $dateEnd . '\' '
                        . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                        . 'AND o.oxstorno = 0 '
                        . 'AND a.oxstorno = 0 '
                     . 'GROUP BY a.oxtitle ' 
                     . 'ORDER BY artcount DESC '; 
 			 
		if ($this->DebugMode) logfile('debug', 'getTopSeller: '.$sql);
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		
		$db = null;

		$i = 0;
		foreach($dbData as $value) {
			$dbData[$i]['artcount'] = intFormat($dbData[$i]['artcount'], $this);
                        $dbData[$i]['artrev'] = currFormat($dbData[$i]['artrev'], $this);
			$i++;
		}
		
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		return $dataTable;
	}
	
	
	// Retrieving the Top Cancels  
	public function getTopCancels($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
						
		$sql = 'SELECT sum(a.oxamount) AS artcount, a.oxartnum AS artno, CONCAT(\'<nobr>\', a.oxtitle, \'</nobr>\') AS arttitle, SUM(a.oxbrutprice) AS artrev '
                     . 'FROM oxorder o, oxorderarticles a ' 
                     . 'WHERE o.OXID = a.OXORDERID ' 
                        . 'AND DATE(o.oxorderdate) >= \'' . $dateStart . '\' '
                        . 'AND DATE(o.oxorderdate)  <= \'' . $dateEnd . '\' '
                        . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                        . 'AND (o.oxstorno = 1 OR a.oxstorno = 1)'
                        . 'GROUP BY a.oxtitle ' 
                     . 'ORDER BY artcount DESC  '; 
 			 
		if ($this->DebugMode) logfile('debug', 'getTopCancels: '.$sql);
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		
		$db = null;
		
		$i = 0;
		foreach($dbData as $value) {
			$dbData[$i]['artcount'] = intFormat($dbData[$i]['artcount'], $this);
                        $dbData[$i]['artrev'] = currFormat($dbData[$i]['artrev'], $this);
			$i++;
		}
		
		// convert this array to a DataTable object
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		return $dataTable;
	}
	
	
	// Retrieving the Sum and Percentage per Age and Payment  
	public function getAgeAnalysis($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* Now correct api behavior, no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}

		$this->AgeClasses[$this->SiteID] = '0-3000|' . $this->AgeClasses[$this->SiteID];
		$ageClasses = explode('|', $this->AgeClasses[$this->SiteID]);
		$data = array();
		
		$sql = array();
		$total = array();
                $andWhere = ' AND u.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '; 
                
		array_push($sql,  'SELECT COUNT(*) AS total FROM oxuser WHERE oxshopid = ' . $this->ShopID[$this->SiteID] . ' ');
		array_push($sql,  'SELECT SUM(o.oxtotalordersum) AS total FROM oxuser u, oxorder o '
						. 'WHERE  u.oxid = o.oxuserid AND o.oxstorno=0 ' . $andWhere );
		array_push($sql,  'SELECT COUNT(*) AS total FROM oxuser u ' 
						. 'WHERE u.oxsal = \'MR\' AND YEAR(u.oxbirthdate) != 0 ' . $andWhere );
		array_push($sql,  'SELECT COUNT(*) AS total FROM oxuser u ' 
						. 'WHERE u.oxsal = \'MRS\' AND YEAR(u.oxbirthdate) != 0 ' . $andWhere );
		array_push($sql,  'SELECT SUM(o.oxtotalordersum) AS total FROM oxuser u, oxorder o '
						. 'WHERE  u.oxid = o.oxuserid AND o.oxstorno=0 AND oxsal = \'MR\' AND YEAR(oxbirthdate) != 0 ' . $andWhere );
		array_push($sql,  'SELECT SUM(o.oxtotalordersum) AS total FROM oxuser u, oxorder o '
						. 'WHERE  u.oxid = o.oxuserid AND o.oxstorno=0 AND oxsal = \'MRS\' AND YEAR(oxbirthdate) != 0 ' . $andWhere );
		$payments = getOxPayments($this);
		foreach ($payments as $payment)
			array_push($sql, 'SELECT SUM(o.oxtotalordersum) AS total FROM oxuser u, oxorder o, oxpayments p '
						. 'WHERE  u.oxid = o.oxuserid AND o.oxpaymenttype = p.oxid AND p.oxdesc=\''.$payment.'\' ' 
						. 'AND o.oxstorno=0 AND YEAR(u.oxbirthdate) != 0 ' . $andWhere );
		
		foreach ($sql as $stmt) {
			if ($this->DebugMode) 
				logfile('debug', 'getAgeAnalysis: '.$stmt);
			$result = $db->query($stmt);
			if (($this->DebugMode) && (is_bool($result)))
				logfile('error', 'getAgeAnalysis: [ERROR] '.$stmt);
			$dbData = $result->fetch(PDO::FETCH_ASSOC);
			if (empty($dbData['total'])) $dbData['total'] = '1.0';
				array_push($total, $dbData['total']);
		}
		if ($this->DebugMode) logfile('debug', $total);

		foreach ($ageClasses as $ageClass) {
			if ($this->DebugMode) logfile('debug', 'getAgeAnalysis: ageClass = '.$ageClass);
			$sql = array();
			if ($ageClass == '0-3000') $totalval=$total[0]; else $totalval=$total[2];
			array_push($sql,  'SELECT COUNT(*) AS totalsum, COUNT(*)/'.$totalval.'*100.0 AS percent ' 
				 			. 'FROM oxuser u ' 
				 			. 'WHERE u.oxsal = \'MR\' ' 
							. 'AND (YEAR(curdate())-YEAR(u.oxbirthdate)) BETWEEN ' . str_replace('-', ' AND ', $ageClass) . $andWhere  );
							
			if ($ageClass == '0-3000') $totalval=$total[0]; else $totalval=$total[3];
			array_push($sql,  'SELECT COUNT(*) AS totalsum, COUNT(*)/'.$totalval.'*100.0 AS percent ' 
				 			. 'FROM oxuser u ' 
				 			. 'WHERE u.oxsal = \'MRS\' ' 
							. 'AND (YEAR(curdate())-YEAR(u.oxbirthdate)) BETWEEN ' . str_replace('-', ' AND ', $ageClass) . $andWhere  );
							
			if ($ageClass == '0-3000') $totalval=$total[1]; else $totalval=$total[4];
			array_push($sql,  'SELECT SUM(o.oxtotalordersum) AS totalsum, SUM(o.oxtotalordersum)/'.$totalval.'*100.0 AS percent '  
							. 'FROM oxuser u, oxorder o '
							. 'WHERE  u.oxid = o.oxuserid '
							. 'AND o.oxstorno=0 '
							. 'AND u.oxsal = \'MR\' ' 
							. 'AND (YEAR(curdate())-YEAR(u.oxbirthdate)) BETWEEN ' . str_replace('-', ' AND ', $ageClass) . $andWhere  );
							
			if ($ageClass == '0-3000') $totalval=$total[1]; else $totalval=$total[5];
			array_push($sql,  'SELECT SUM(o.oxtotalordersum) AS totalsum, SUM(o.oxtotalordersum)/'.$totalval.'*100.0 AS percent '  
							. 'FROM oxuser u, oxorder o '
							. 'WHERE  u.oxid = o.oxuserid '
							. 'AND o.oxstorno=0 '
							. 'AND u.oxsal = \'MRS\' ' 
							. 'AND (YEAR(curdate())-YEAR(u.oxbirthdate)) BETWEEN ' . str_replace('-', ' AND ', $ageClass) . $andWhere  );
							
			$i = 5;
			foreach ($payments as $payment) {
				$i++;
				if ($ageClass == '0-3000') $totalval=$total[1]; else $totalval=$total[$i];
				array_push($sql, 'SELECT SUM(o.oxtotalordersum) AS totalsum, SUM(o.oxtotalordersum)/'.$totalval.'*100.0 AS percent '  
							. 'FROM oxuser u, oxorder o, oxpayments p '
							. 'WHERE u.oxid = o.oxuserid '
							. 'AND o.oxpaymenttype = p.oxid '
							. 'AND p.oxdesc=\''.$payment.'\' '
							. 'AND o.oxstorno=0 '
							. 'AND (YEAR(curdate())-YEAR(u.oxbirthdate)) BETWEEN ' . str_replace('-', ' AND ', $ageClass) . $andWhere  );
			}
			
			if ($this->DebugMode) $sumval = array();
			$percentage = array();
			foreach ($sql as $stmt) {
				if ($this->DebugMode) 
					logfile('debug', 'getAgeAnalysis: '.$stmt);
				$result = $db->query($stmt);
				if (($this->DebugMode) && (is_bool($result)))
					logfile('error', 'getAgeAnalysis: [ERROR] '.$stmt);
				$dbData = $result->fetch(PDO::FETCH_ASSOC);
				if (empty($dbData['percent'])) $dbData['percent'] = '0.0';
				array_push($percentage, $dbData['percent']);
				if ($this->DebugMode) 
					array_push($sumval, $dbData['totalsum']);
			}
			if ($this->DebugMode) logfile('debug', 'SUMVAL');
			if ($this->DebugMode) logfile('debug', $sumval);
							
			if ($ageClass == '0-3000') {
				$ageClass = 'All';
				$bold = '<b>';
				$this->Style = 'font-weight:bold;';
			} else {
				$bold = '';
				$this->Style = '';
			}
			$temp = array('ageclass' => $bold . str_replace('-', '&nbsp;-&nbsp;', $ageClass), 
				'mencount' => percFormat($percentage[0], $this), 
				'womencount' => percFormat($percentage[1], $this), 
				'menrevenue' => percFormat($percentage[2], $this), 
				'womenrevenue' => percFormat($percentage[3], $this));
			$i = 3;
			foreach ($payments as $payment) {
				$i++;
				$temp[$payment] = percFormat($percentage[$i], $this);
			}
			
			array_push($data, $temp);
		}
		
		$db = null;
		
		// convert this array to a DataTable object
		if ($this->DebugMode) logfile('debug', $data);
		$dataTable = new Piwik_DataTable();
		$dataTable->addRowsFromArrayWithIndexLabel($data);
		return $dataTable;
	}
	
	
	// Retrieving the Sum and Percentage per Age and Payment  
	public function getStoreStatus($date, $period)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
		// oxstock     - amount in store
		// oxstockflag - 1: default
		//               2: offline when oxstock=0
		//               3: not buyable when oxstock=0
		//               4: foreign store
		// oxdelivery  - date when deliverable again
		// oxremindamount - amount when low email is sent
		// oxstocktext 
		// oxnostocktext 
		
		$dataTable = new Piwik_DataTable();
		
		$sql = 'SELECT oxartnum, CONCAT(\'<nobr>\', oxtitle, \'</nobr>\') AS oxtitle, '
			 . 'CONCAT(\'<nobr>\', oxvarselect, \'</nobr>\') AS oxvarselect, oxstock, oxstocktext, '
			 . 'CONCAT(\'<nobr>\', oxnostocktext, \'</nobr>\') as oxnostocktext, oxstockflag, oxdelivery, '
			 . 'oxremindamount, oxvarcount, oxstock '
			 . 'FROM oxarticles '
			 . 'WHERE oxstock = 0 '
			 . 'AND oxactive = 1 '
			 . 'AND oxvarcount = 0 '
                         . 'AND oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
			 . 'AND oxparentid = \'\' ';
		if ($this->DebugMode) logfile('debug', 'getStoreStatus: '.$sql);
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		if ($this->DebugMode) logfile('debug', $dbData);

		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$sql = 'SELECT a.oxartnum, '
			 . 'CONCAT(\'<nobr>\', (SELECT b.oxtitle FROM oxarticles b WHERE a.oxparentid = b.oxid), \'</nobr>\') AS oxtitle, '
			 . 'CONCAT(\'<nobr>\', a.oxvarselect, \'</nobr>\') AS oxvarselect, a.oxstock, a.oxstocktext, '
			 . 'CONCAT(\'<nobr>\', a.oxnostocktext, \'</nobr>\') AS oxnostocktext, '
			 . 'a.oxstockflag, a.oxdelivery, a.oxremindamount, a.oxvarcount, oxstock ' 
			 . 'FROM oxarticles a '
			 . 'WHERE a.oxstock = 0 '
			 . 'AND a.oxactive = 1 '
                         . 'AND oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
			 . 'AND a.oxparentid != \'\' ';
		if ($this->DebugMode) logfile('debug', 'getStoreStatus: '.$sql);
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		if ($this->DebugMode) logfile('debug', $dbData);
		
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;
	}
	
	
	// Retrieving the Sum and Percentage per Weekday or Daytime 
	public function getDaytimeAnalysis($date, $period)
	{
		return $this->getTimeAnalysis('daytime');
	}
	
	
	// Retrieving the Sum and Percentage per Weekday or Daytime 
	public function getWeekdayAnalysis($date, $period)
	{
		return $this->getTimeAnalysis('weekday');
	}
	
	
	// Retrieving the Sum and Percentage per Weekday or Daytime 
	public function getTimeAnalysis($timeType)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}

                switch ($period) {

                    case 'day':
                        $whereTime = 'date(o.oxorderdate) = \''. $dateStart.'\' ';
                        break;

                    case 'range':
                    case 'week':
                    case 'month':
                    case 'year':
                        $whereTime = 'date(o.oxorderdate) >= \''. $dateStart.'\' AND  date(o.oxorderdate) <= \''. $dateEnd.'\' ';
                        break;

                }
		
		$dataTable = new Piwik_DataTable();
		
                $sql = 'SELECT SUM(IF(d.oxstorno=0,d.oxbrutprice,0)) AS revenue,  SUM(IF(d.oxstorno=1,d.oxbrutprice,0)) AS cancel '
                        . 'FROM oxorderarticles d, oxorder o '
                        . 'WHERE o.oxid = d.oxorderid '
                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                            . 'AND ' . $whereTime . ' ';
		if ($this->DebugMode) logfile('debug', 'getTimeAnalysis: '.$sql);
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		if ($this->DebugMode) logfile('debug', $dbData);
                $totalRevenue = $dbData[0]['revenue'];
                $totalCancel = $dbData[0]['cancel'];
		if ($this->DebugMode) logfile('debug', 'getTimeAnalysis: totalRevenue='.$totalRevenue);
		if ($this->DebugMode) logfile('debug', 'getTimeAnalysis: totalCancel='.$totalCancel);

                if ($timeType == 'weekday') {
                    $sql = 'SELECT DAYNAME(o.oxorderdate) AS dayname, WEEKDAY(o.oxorderdate) AS inum, '
                         . 'SUM(IF(d.oxstorno=0,d.oxbrutprice,0)) AS revenue,  SUM(IF(d.oxstorno=1,d.oxbrutprice,0)) AS cancel, '
                         . 'SUM(IF(d.oxstorno=0,d.oxbrutprice,0))/'.$totalRevenue.'*100.0 AS revshare,  SUM(IF(d.oxstorno=1,d.oxbrutprice,0))/'.$totalCancel.'*100.0 AS cancshare, '
                         . 'SUM(IF(d.oxstorno=1,d.oxbrutprice,0))/SUM(IF(d.oxstorno=0,d.oxbrutprice,0))*100.0 AS cancpercent '
                         . 'FROM oxorderarticles d, oxorder o '
                         . 'WHERE o.oxid = d.oxorderid '
                             . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                             . 'AND ' . $whereTime . ' '
                         . 'GROUP BY WEEKDAY(o.oxorderdate) '
                         . 'ORDER BY WEEKDAY(o.oxorderdate) ';
                } else {
                    $sql = 'SELECT TIME_FORMAT(o.oxorderdate, \'%H:00\') as daytime, TIME_FORMAT(o.oxorderdate, \'%k\') as inum, '
                         . 'SUM(IF(d.oxstorno=0,d.oxbrutprice,0)) AS revenue,  SUM(IF(d.oxstorno=1,d.oxbrutprice,0)) AS cancel, '
                         . 'SUM(IF(d.oxstorno=0,d.oxbrutprice,0))/'.$totalRevenue.'*100.0 AS revshare,  SUM(IF(d.oxstorno=1,d.oxbrutprice,0))/'.$totalCancel.'*100.0 AS cancshare, '
                         . 'SUM(IF(d.oxstorno=1,d.oxbrutprice,0))/SUM(IF(d.oxstorno=0,d.oxbrutprice,0))*100.0 AS cancpercent '
                         . 'FROM oxorderarticles d, oxorder o '
                         . 'WHERE o.oxid = d.oxorderid '
                             . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                             . 'AND ' . $whereTime . ' '
                         . 'GROUP BY TIME_FORMAT(o.oxorderdate, \'%H\') '
                         . 'ORDER BY TIME_FORMAT(o.oxorderdate, \'%H\') ';
                }
		if ($this->DebugMode) logfile('debug', 'getTimeAnalysis: '.$sql);
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);
		if ($this->DebugMode) logfile('debug', $dbData);

                $i = 0;
		foreach ($dbData as $value) {
                    if (($dbData[$i]['revenue']==0.0) && ($dbData[$i]['cancel']!=0.0))
                        $dbData[$i]['cancpercent'] = 100.0;
                    $dbData[$i]['revenue'] = currFormat($dbData[$i]['revenue'], $this);
                    $dbData[$i]['cancel'] = currFormat($dbData[$i]['cancel'], $this);
                    $dbData[$i]['revshare'] = percFormat($dbData[$i]['revshare'], $this);
                    $dbData[$i]['cancshare'] = percFormat($dbData[$i]['cancshare'], $this);
                    $dbData[$i]['cancpercent'] = percFormat($dbData[$i]['cancpercent'], $this);
                    $i++;
		}
		if ($this->DebugMode) logfile('debug', $dbData);
                
		if ($timeType == 'weekday') {
                    //$maxCount = 7;   //days per week
                    $weekdays = array(
                        '<span style="display:none;">0</span>'.Piwik_Translate('OxidPlugin_Monday'), 
                        '<span style="display:none;">1</span>'.Piwik_Translate('OxidPlugin_Tuesday'), 
                        '<span style="display:none;">2</span>'.Piwik_Translate('OxidPlugin_Wednesday'), 
                        '<span style="display:none;">3</span>'.Piwik_Translate('OxidPlugin_Thursday'), 
                        '<span style="display:none;">4</span>'.Piwik_Translate('OxidPlugin_Friday'), 
                        '<span style="display:none;">5</span>'.Piwik_Translate('OxidPlugin_Saturday'), 
                        '<span style="display:none;">6</span>'.Piwik_Translate('OxidPlugin_Sunday'));
                    
                    // if no record returned create one entry with zeros
                    if (count($dbData) == 0)
                        array_push($dbData, array(
                            'inum' => 0,
                            'dayname' => $weekdays[0], 
                            'revenue' => currFormat(0.0, $this), 
                            'revshare' => percFormat(0.0, $this), 
                            'cancel' => currFormat(0.0, $this), 
                            'cancshare' => percFormat(0.0, $this),  
                            'cancpercent' => percFormat(0.0, $this) ));
                    $k = 0;
                    for ($i=0; $i<7; $i++)
                        if ( $dbData[$k]['inum'] != $i )
                            array_push($dbData, array(
                                'inum' => $i,
                                'dayname' => $weekdays[$i], 
                                'revenue' => currFormat(0.0, $this), 
                                'revshare' => percFormat(0.0, $this), 
                                'cancel' => currFormat(0.0, $this), 
                                'cancshare' => percFormat(0.0, $this),  
                                'cancpercent' => percFormat(0.0, $this) ));
                        else {
                            $dbData[$k]['dayname'] = $weekdays[$i];
                            if(count($dbData)-1 > $k) 
                                $k++;
                        }
                } else {
                    //$max = 24;  //hours per day
                    
                    // if no record returned create one entry with zeros
                    if (count($dbData) == 0)
                        array_push($dbData, array(
                            'inum' => $i,
                            'daytime' => str_pad(0, 2, '0', STR_PAD_LEFT).':00', 
                            'revenue' => currFormat(0.0, $this), 
                            'revshare' => percFormat(0.0, $this), 
                            'cancel' => currFormat(0.0, $this), 
                            'cancshare' => percFormat(0.0, $this),  
                            'cancpercent' => percFormat(0.0, $this) ));

                    $k = 0;
                    for ($i=0; $i<24; $i++)
                        if ( $dbData[$k]['inum'] != $i )
                            array_push($dbData, array(
                                'inum' => $i,
                                'daytime' => str_pad($i, 2, '0', STR_PAD_LEFT).':00', 
                                'revenue' => currFormat(0.0, $this), 
                                'revshare' => percFormat(0.0, $this), 
                                'cancel' => currFormat(0.0, $this), 
                                'cancshare' => percFormat(0.0, $this),  
                                'cancpercent' => percFormat(0.0, $this) ));
                        else
                            if(count($dbData)-1 > $k)
                                $k++;
                }
                


		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
if ($this->DebugMode) logfile('debug', '------------------dataTable-----------------');
if ($this->DebugMode) logfile('debug', $dataTable);
		
		$db = null;
		
		return $dataTable;
                
        }
	
	
	// Retrieving the Sum and Percentage per Weekday or Daytime 
	public function getDaytimeGraph($date, $period)
	{
include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
if ($this->DebugMode) logfile('debug', 'getDaytimeGraph(): date');
if ($this->DebugMode) logfile('debug', 'date='.$date);
            return $this->getTimeAnalysisGraph($date, $period, 'daytime');
	}
	
	
	// Retrieving the Sum and Percentage per Weekday or Daytime 
	public function getWeekdayGraph($date, $period)
	{
if ($this->DebugMode) logfile('debug', 'getWeekdayGraph(): date');
if ($this->DebugMode) logfile('debug', 'date='.$date);
            return $this->getTimeAnalysisGraph($date, $period, 'weekday');
            
	}
        

        // Retrieving the Sum and Percentage per Weekday or Daytime 
	public function getTimeAnalysisGraph($date, $period, $timeType = 'weekday')
	{
            //error_reporting(E_ALL);
            include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';

if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph(): date');
if ($this->DebugMode) logfile('debug', 'date='.$date);
            $this->SiteID = Piwik_Common::getRequestVar('idSite');
            $db = openDB($this);

            $date = Piwik_Common::getRequestVar('date');
if ($this->DebugMode) logfile('debug', 'date='.$date);
            $period = Piwik_Common::getRequestVar('period');
            $this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/

if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph(): vor Range');
            if ($period == 'range') {
                $dateStart = substr($date, 0, strpos($date, ',')); 
                $dateEnd = substr($date, strpos($date, ',')+1);  
            } else {
if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph(): vor requestedDate = ..');
                //$requestedDate = Piwik_Date::factory($date);
if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph(): requestedDate = '.$requestedDate);
                //$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
                $requestedPeriod = new Piwik_Period_Range($period, $date);
if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph(): vor requestedPeriod = '.$requestedPeriod);
                $dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph(): vor dateStart = '.$dateStart);
                $dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph(): vor dateEnd = '.$dateEnd);
            }

if ($this->DebugMode) logfile('debug', 'vor Switch');
                switch ($period) {

                    case 'day':
                        $whereTime = 'date(o.oxorderdate) = \''. $dateStart.'\' ';
                        break;

                    case 'range':
                    case 'week':
                    case 'month':
                    case 'year':
                        $whereTime = 'date(o.oxorderdate) >= \''. $dateStart.'\' AND  date(o.oxorderdate) <= \''. $dateEnd.'\' ';
                        break;

                }
		
                $sql = 'SELECT SUM(IF(d.oxstorno=0,d.oxbrutprice,0)) AS revenue,  SUM(IF(d.oxstorno=1,d.oxbrutprice,0)) AS cancel '
                        . 'FROM oxorderarticles d, oxorder o '
                        . 'WHERE o.oxid = d.oxorderid '
                            . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                            . 'AND ' . $whereTime . ' ';
		if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph: '.$sql);
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll();
		if ($this->DebugMode) logfile('debug', $dbData);
                $totalRevenue = $dbData[0]['revenue'];
                $totalCancel = $dbData[0]['cancel'];
		if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph: totalRevenue='.$totalRevenue);
		if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph: totalCancel='.$totalCancel);

                if ($timeType == 'weekday') {
                    $sql = 'SELECT WEEKDAY(o.oxorderdate) AS inum, DAYNAME(o.oxorderdate) AS dayname, '
                         . 'SUM(IF(d.oxstorno=0,d.oxbrutprice,0)) AS revenue,  SUM(IF(d.oxstorno=1,d.oxbrutprice,0)) AS cancel '
                         . 'FROM oxorderarticles d, oxorder o '
                         . 'WHERE o.oxid = d.oxorderid '
                             . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                             . 'AND ' . $whereTime . ' '
                         . 'GROUP BY WEEKDAY(o.oxorderdate) '
                         . 'ORDER BY WEEKDAY(o.oxorderdate) ';
                } else {
                    $sql = 'SELECT TIME_FORMAT(o.oxorderdate, \'%k\') as inum, TIME_FORMAT(o.oxorderdate, \'%H:00\') as daytime, '
                         . 'SUM(IF(d.oxstorno=0,d.oxbrutprice,0)) AS revenue, SUM(IF(d.oxstorno=1,d.oxbrutprice,0)) AS cancel '
                         . 'FROM oxorderarticles d, oxorder o '
                         . 'WHERE o.oxid = d.oxorderid '
                             . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                             . 'AND ' . $whereTime . ' '
                         . 'GROUP BY TIME_FORMAT(o.oxorderdate, \'%H\') '
                         . 'ORDER BY TIME_FORMAT(o.oxorderdate, \'%H\') ';
                }
		if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph: '.$sql);
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);
		if ($this->DebugMode) logfile('debug', $dbData);

		$dbGraph = array();
                if ($timeType == 'weekday') {
                    //$maxCount = 7;   //days per week  
                    $weekdays = array(
                        Piwik_Translate('OxidPlugin_Monday'), 
                        Piwik_Translate('OxidPlugin_Tuesday'), 
                        Piwik_Translate('OxidPlugin_Wednesday'), 
                        Piwik_Translate('OxidPlugin_Thursday'), 
                        Piwik_Translate('OxidPlugin_Friday'), 
                        Piwik_Translate('OxidPlugin_Saturday'), 
                        Piwik_Translate('OxidPlugin_Sunday') );
                    $k = 0;
                    for ($i=0; $i<7; $i++)
                        if ( $dbData[$k]['inum'] != $i ) {
                            $dbGraph[$weekdays[$i]] = array('revenue' => 0.0, 'cancel' => 0.0);
                        } else {
                            $dbGraph[$weekdays[$i]] = array('revenue' => $dbData[$k]['revenue'], 'cancel' => $dbData[$k]['cancel']);
                            if (count($dbData)-1 > $k)
                                $k++;
                        }
                } else {
                    //$max = 24;  //hours per day
                    $k = 0;
                    for ($i=0; $i<24; $i++)
                        if ( $dbData[$k]['inum'] != $i ) {
                            if ($this->DebugMode) logfile('debug', 'dbGraph: 0.0');
                            $dbGraph[str_pad($i, 2, '0', STR_PAD_LEFT).':00'] = array('revenue' => 0.0, 'cancel' => 0.0);
                        } else {
                            $dbGraph[$dbData[$k]['daytime']] = array('revenue' => $dbData[$k]['revenue'], 'cancel' => $dbData[$k]['cancel']);
                            if (count($dbData)-1 > $k)
                                $k++;
                        }
                }/*--*/
                if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph/dbGraph---------------------------');
		if ($this->DebugMode) logfile('debug', $dbGraph);
                if ($this->DebugMode) logfile('debug', 'getTimeAnalysisGraph/dbGraph---------------------------');

		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbGraph);
		
		$db = null;
		
		return $dataTable;
                //return;
                
        }
	
	
	// Retrieving the Sum and Count per Manufacturer  
	public function getManufacturerRevenue($date, $period)
	{
		return $this->getDelivererRevenue('oxmanufacturer');
	}
	
	
	// Retrieving the Sum per Manufacturer for  Bar Chart Display  
	public function getManufacturerRevenueGraph($date, $period)
	{

		return $this->getDelivererRevenueGraph('oxmanufacturer');
	}
	
	
	// Retrieving the Sum and Count per Vendor  
	public function getVendorRevenue($date, $period)
	{
		return $this->getDelivererRevenue('oxvendor');
	}
	
	
	// Retrieving the Sum per Vendor for  Bar Chart Display  
	public function getVendorRevenueGraph($date, $period)
	{
		return $this->getDelivererRevenueGraph('oxvendor');
	}
	
	
	// Retrieving the Sum and Count per Deliverer (Manufacturer or Vendor)  
	public function getDelivererRevenue($delivererType)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
		
		if ($delivererType == 'oxmanufacturer') {
			$delivererid = 'oxmanufacturerid';
			$delivererTable = 'oxmanufacturers';
		}
		else {
			$delivererid = 'oxvendorid';
			$delivererTable = 'oxvendor';
		}
		
		$sql = 'SELECT oxid, oxtitle FROM ' . $delivererTable . ' '
                     . 'WHERE oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                     . 'ORDER BY oxtitle ASC';
		$result = $db->prepare($sql);
		$result->execute();
		$Deliverers = $result->fetchAll(PDO::FETCH_NAMED);
		if ($this->DebugMode) logfile('debug', $sql);
		if ($this->DebugMode) logfile('debug', $Deliverers);
		
                switch ($period) {

                    case 'day':
                        $whereTime = 'date(o.oxorderdate) = \''. $dateStart.'\' ';
                        break;

                    case 'range':
                    case 'week':
                    case 'month':
                    case 'year':
                        $whereTime = 'date(o.oxorderdate) >= \''. $dateStart.'\' AND  date(o.oxorderdate) <= \''. $dateEnd.'\' ';
                        break;

                }

                $dbData = array();
		$i = 0;
		$totalbrutsum = 0.001; //avoid div0 when total revenue is zero
		$totalnetmargin = 0.001; //avoid div0 when total revenue is zero
		foreach ($Deliverers as $deliverer) {
			
                        $sql = 'SELECT \''.$deliverer['oxtitle'].'\', SUM(d.oxamount) AS totalcount, '
                                    . '(SUM(d.oxnetprice) - '
                                        . 'SUM(d.oxamount * a.oxbprice)'
                                    . ') AS netmargin, '
                                    . 'SUM(d.oxamount*d.oxprice) AS brutpricesum '
                                . 'FROM oxarticles a, oxorder o, oxorderarticles d '
                                . 'WHERE '
                                    . 'a.oxid = d.oxartid '
                                    . 'AND o.oxid = d.oxorderid '
                                    . 'AND a.'.$delivererid.'=\''.$deliverer['oxid'].'\''
                                    . 'AND d.oxstorno = 0 '
                                    . 'AND a.oxparentid = \'\' '
                                    . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                    . 'AND '.$whereTime.' ';
                
                        $result = $db->prepare($sql);
			$result->execute();
			$dbSingleVal = $result->fetchAll(PDO::FETCH_NAMED);
			if ($this->DebugMode) logfile('debug', $deliverer['oxtitle']);
			if ($this->DebugMode) logfile('debug', $sql);
			if ($this->DebugMode) logfile('debug', $dbSingleVal);
			$dbData[$i]['deliverer'] = $deliverer['oxtitle'];
			if (!empty($dbSingleVal[0]['brutpricesum'])) {
				$dbData[$i]['totalcount'] = $dbSingleVal[0]['totalcount'];
				$dbData[$i]['netmargin'] = $dbSingleVal[0]['netmargin'];
				$dbData[$i]['brutsum'] = $dbSingleVal[0]['brutpricesum'];
				$totalbrutsum += $dbData[$i]['brutsum'];
				$totalnetmargin += $dbData[$i]['netmargin'];
				//$dbData[$manufacturer['oxtitle']] = $dbSingleVal[0]['oxbrutpricesum'];
			} else {
				$dbData[$i]['totalcount'] = 0;
				$dbData[$i]['netmargin'] = 0.00;
				$dbData[$i]['brutsum'] = 0.00;
				//$dbData[$manufacturer['oxtitle']] = 0.0;
			}
			
                        
                        // Variants of articles 
                        /*******/
			$sql = 'SELECT '
                                    . '(SELECT b.'.$delivererid.' FROM oxarticles b WHERE  a.oxparentid = b.oxid) AS delivererid, ' 
                                    . 'SUM(d.oxamount) AS totalcount, ' 
                                    . '(SUM(d.oxnetprice) - '
                                    . 'SUM(d.oxamount * '
                                        . 'IF(a.oxbprice=0.0,'
                                            . '(SELECT a2.oxbprice FROM oxarticles a2 WHERE a2.oxid = a.oxparentid),'
                                            . 'a.oxbprice))'
                                    . ') AS netmargin, '
                                    . 'SUM(d.oxbrutprice) AS brutpricesum ' 
				 . 'FROM '
                                    . 'oxorderarticles d, oxorder o, oxarticles a '
				 . 'WHERE '
                                    . 'o.oxid = d.oxorderid '
                                    . 'AND d.oxartid = a.oxid '
                                    . 'AND a.oxparentid != \'\' '
                                    . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                    . 'AND '.$whereTime.' ' 
                                    . 'AND o.oxstorno = 0 ' 
                                    . 'AND d.oxstorno = 0 ' 
                                    . 'AND (SELECT b.'.$delivererid.' FROM oxarticles b ' 
                                            . 'WHERE  a.oxparentid = b.oxid ' 
                                            . 'AND b.'.$delivererid.' = \''.$deliverer['oxid'].'\') = '
                                            . '\''.$deliverer['oxid'].'\' '
				. 'GROUP BY (SELECT b.'.$delivererid.' FROM oxarticles b '
					. 'WHERE  a.oxparentid = b.oxid) '
				. 'ORDER BY (SELECT b.'.$delivererid.' FROM oxarticles b '
					. 'WHERE  a.oxparentid = b.oxid) ';
				//. 'ORDER BY '.$delivererid.' ';
			$result = $db->prepare($sql);
			$result->execute();
			$dbSingleVal = $result->fetchAll(PDO::FETCH_NAMED);
			if ($this->DebugMode) logfile ('debug', $sql);
			if ($this->DebugMode) logfile ('debug', $dbSingleVal);
			if ($this->DebugMode) logfile ('debug', $result->rowCount());
			$dbData[$i]['deliverer'] = $deliverer['oxtitle'];
			if (!empty($dbSingleVal[0]['brutpricesum'])) {
				$dbData[$i]['totalcount'] += $dbSingleVal[0]['totalcount'];
				$dbData[$i]['brutsum'] += $dbSingleVal[0]['brutpricesum'];
				$dbData[$i]['netmargin'] += $dbSingleVal[0]['netmargin'];
				$totalbrutsum += $dbData[$i]['brutsum'];
				$totalnetmargin += $dbData[$i]['netmargin'];
			} else {
				$dbData[$i]['totalcount'] += 0;
				$dbData[$i]['brutsum'] += 0.00;
			}
                         /******/

			
			if ($dbData[$i]['totalcount'] != 0)
                            $i++;
		}

                $iMax = count($dbData) - 1;
                if ($dbData[$iMax]['totalcount'] == 0) {
                    unset($dbData[$iMax]);
                    $iMax = $iMax - 1;
                }

                for ($i=0; $i<=$iMax; $i++) {
			$dbData[$i]['percentnet'] = percFormat( $dbData[$i]['netmargin'] / $totalnetmargin * 100.0, $this );
			$dbData[$i]['percentbrut'] = percFormat( $dbData[$i]['brutsum'] / $totalbrutsum * 100.0, $this );
			$dbData[$i]['totalcount'] = intFormat($dbData[$i]['totalcount'], $this);
			$dbData[$i]['netmargin'] = currFormat($dbData[$i]['netmargin'], $this);
			$dbData[$i]['brutsum'] = currFormat($dbData[$i]['brutsum'], $this);
		}
		
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;
	}
	
	
	// Retrieving the Sum per Deliverer (manufacturer or vendor) for Bar Chart  
	public function getDelivererRevenueGraph($delivererType)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
		
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$db = openDB($this);

		$date = Piwik_Common::getRequestVar('date');
		$period = Piwik_Common::getRequestVar('period');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		
		// works wrong for period=range: $period = Piwik_Common::getRequestVar('period');
		// Work around for the this bug
                /* no longer necessary
		if (strpos($date, ',') > 0)
			$period = 'range';*/
		
		if ($period == 'range') {
			$dateStart = substr($date, 0, strpos($date, ',')); 
			$dateEnd = substr($date, strpos($date, ',')+1);  
		} else {
			$requestedDate = Piwik_Date::factory($date);
			$requestedPeriod = Piwik_Period::factory($period, $requestedDate);
			$dateStart = $requestedPeriod->getDateStart()->toString('Y-m-d'); 
			$dateEnd = $requestedPeriod->getDateEnd()->toString('Y-m-d');   
		}
		
		if ($delivererType == 'oxmanufacturer') {
			$delivererid = 'oxmanufacturerid';
			$delivererTable = 'oxmanufacturers';
		}
		else {
			$delivererid = 'oxvendorid';
			$delivererTable = 'oxvendor';
		}
		
		$sql = 'SELECT oxid, oxtitle FROM ' . $delivererTable . ' '
                     . 'WHERE oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                     . 'ORDER BY oxtitle ASC';
		$result = $db->prepare($sql);
		$result->execute();
		$Deliverers = $result->fetchAll(PDO::FETCH_NAMED);
		if ($this->DebugMode) logfile('debug', $Deliverers);

                switch ($period) {

                    case 'day':
                        $whereTime = 'date(o.oxorderdate) = \''. $dateStart.'\' ';
                        break;

                    case 'range':
                    case 'week':
                    case 'month':
                    case 'year':
                        $whereTime = 'date(o.oxorderdate) >= \''. $dateStart.'\' AND  date(o.oxorderdate) <= \''. $dateEnd.'\' ';
                        break;

                }
                
		$dbData = array();
		$i = 0;
		foreach ($Deliverers as $deliverer) {
			
			$sql = 'SELECT '
                                    . '\''.$deliverer['oxtitle'].'\', '
                                    .'SUM(d.oxbrutprice) AS oxbrutpricesum '
				 . 'FROM oxorderarticles d, oxorder o, oxarticles a '
				 . 'WHERE '
                                    . 'd.oxorderid = o.oxid '
                                    . 'AND '.$whereTime.' '
                                    . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                    . 'AND o.oxstorno = 0 ' 
                                    . 'AND d.oxartid = a.oxid '
                                    . 'AND a.oxparentid = \'\' '
                                    . 'AND a.'.$delivererid.'=\''.$deliverer['oxid'].'\'';

                        $sql = 'SELECT '
                                    . '\''.$deliverer['oxtitle'].'\', '
                                    . '(SUM(d.oxnetprice) - '
                                        . 'SUM(d.oxamount * a.oxbprice)'
                                    . ') AS netmargin, '
                                    . 'SUM(d.oxamount*d.oxprice) AS brutpricesum '
                                . 'FROM oxarticles a, oxorder o, oxorderarticles d '
                                . 'WHERE '
                                    .'a.oxid = d.oxartid '
                                    . 'AND o.oxid = d.oxorderid '
                                    . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
                                    . 'AND a.'.$delivererid.'=\''.$deliverer['oxid'].'\''
                                    . 'AND d.oxstorno = 0 '
                                    . 'AND a.oxparentid = \'\' '
                                    . 'AND '.$whereTime.' ';
                        
                        
                        $result = $db->prepare($sql);
			$result->execute();
			$dbSingleVal = $result->fetchAll(PDO::FETCH_NAMED);
			if ($this->DebugMode) logfile('debug', $deliverer['oxtitle']);
			if ($this->DebugMode) logfile('debug', $sql);
			if ($this->DebugMode) logfile('debug', $dbSingleVal);
			if (!empty($dbSingleVal[0]['brutpricesum'])) {
				$dbData[$deliverer['oxtitle']] = $dbSingleVal[0]['brutpricesum'];
			}
			
			$sql = 'SELECT '
					. '(SELECT b.'.$delivererid.' FROM oxarticles b '
					. 'WHERE  a.oxparentid = b.oxid) AS '.$delivererid.', ' 
					. 'SUM(d.oxbrutprice) AS brutpricesum ' 
					. 'FROM oxorderarticles d, oxorder o, oxarticles a '
				 . 'WHERE o.oxid = d.oxorderid '
				 . 'AND d.oxartid = a.oxid '
				 . 'AND a.oxparentid != \'\' '
				 . 'AND '.$whereTime.' ' 
                                 . 'AND o.oxshopid = ' . $this->ShopID[$this->SiteID] . ' '
				 . 'AND o.oxstorno = 0 ' 
				 . 'AND (SELECT b.'.$delivererid.' FROM oxarticles b ' 
					. 'WHERE  a.oxparentid = b.oxid ' 
					. 'AND b.'.$delivererid.' = \''.$deliverer['oxid'].'\') = '
					. '\''.$deliverer['oxid'].'\' '
				. 'GROUP BY '.$delivererid.' '
				. 'ORDER BY '.$delivererid.' ';
			$result = $db->prepare($sql);
			$result->execute();
			$dbSingleVal = $result->fetchAll(PDO::FETCH_NAMED);
			if ($this->DebugMode) logfile ('debug', $sql);
			if ($this->DebugMode) logfile ('debug', $dbSingleVal);
			if (!empty($dbSingleVal[0]['brutpricesum'])) {
				if (empty($dbData[$deliverer['oxtitle']]))
					$dbData[$deliverer['oxtitle']] = $dbSingleVal[0]['brutpricesum'];
				else
					$dbData[$deliverer['oxtitle']] += $dbSingleVal[0]['brutpricesum'];
			}
			
			$i++;
		}
		
		arsort($dbData, SORT_NUMERIC);
		while (count($dbData) >= $this->MaxBarChartColumns) {
			array_pop($dbData);
		}
		if ($this->DebugMode) logfile('debug', 'getDelivererRevenueGraph/dbData-----------------------');
		if ($this->DebugMode) logfile('debug', $dbData);
		if ($this->DebugMode) logfile('debug', 'getDelivererRevenueGraph/dbData-----------------------');
				
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;
	}

        
        function getVoucherUse()
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
				
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		//$siteCurrency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));

                $db = openDB($this);
		
		/*
                $sql = "SELECT v.oxvouchernr, s.oxseriedescription, s.oxdiscount, s.oxdiscounttype, "
                            . "COUNT(*) AS totalcount, SUM(IF(v.oxdateused='0000-00-00',0,1)) AS usedcount, "
                            . "(TO_DAYS(MIN(v.oxdateused))-TO_DAYS(MAX(v.oxdateused))+1) AS days, "
                            . "SUM(o.oxtotalordersum) AS ordersum, AVG(o.oxtotalordersum) AS average "
                        . "FROM oxvouchers v "
                            . "LEFT JOIN oxorder o "
                                . "ON v.oxorderid = o.oxid "
                            . "LEFT JOIN oxvoucherseries s "
                                . "ON v.oxvoucherserieid = s.oxid "
                        . "GROUP BY v.oxvouchernr "
                        . "ORDER BY ordersum DESC "
                        . "LIMIT 0, 500 "; 
                 */
                
                $sql = "SELECT o.oxid, v.oxdateused, (TO_DAYS(NOW())-TO_DAYS(v.oxdateused)) AS days, s.oxdiscount, s.oxdiscounttype, "
                            . "CONCAT('<nobr>', v.oxvouchernr, '</nobr>') AS oxvouchernr, s.oxseriedescription, "
                            . "o.oxordernr, v.oxorderid, CONCAT(o.oxbillfname, '&nbsp;', o.oxbilllname) AS name, o.oxtotalordersum, " 
                            . "u.oxusername, u.oxstreet, u.oxstreetnr, u.oxzip, u.oxcity "
                        . "FROM oxvouchers v, oxorder o, oxvoucherseries s, oxuser u "
                        . "WHERE v.oxdateused != '0000-00-00' "
                            . "AND v.oxorderid = o.oxid "
                            . "AND v.oxvoucherserieid = s.oxid "
                            . "AND o.oxuserid = u.oxid "
                            . "AND o.oxshopid = {$this->ShopID[$this->SiteID]} "
                        . "ORDER BY v.oxdateused DESC "
                        . "LIMIT 0, 500 ";
                
                if ($this->DebugMode) logfile('debug', $sql);
                $stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);

                $i = 0;
		foreach($dbData as $value) {
                    $dbData[$i]['oxdiscount'] = currFormat($dbData[$i]['oxdiscount'], $this);
                    $dbData[$i]['oxtotalordersum'] = currFormat($dbData[$i]['oxtotalordersum'], $this);

                    $sql = 'SELECT oxamount, oxtitle '
                            . 'FROM oxorderarticles '
                            . 'WHERE oxorderid = \''.$dbData[$i]['oxid'].'\' '
                                . 'AND oxstorno = 0 ';
                    $stmt = $db->prepare($sql);
                    $stmt->execute();
                    $details = $stmt->fetchAll(PDO::FETCH_NAMED);
                    $dbData[$i]['oxdetails'] = Piwik_Translate('OxidPlugin_Order') . ':';
                    foreach ($details as $detail) {
                        $dbData[$i]['oxdetails'] = $dbData[$i]['oxdetails'] . chr(13) . $detail['oxamount'] . ' x ' . $detail['oxtitle'];
                    }
                    $dbData[$i]['oxvouchernr'] = addTitle($dbData[$i]['oxvouchernr'], $dbData[$i]['oxseriedescription']);
                    $dbData[$i]['oxtotalordersum'] = addTitle($dbData[$i]['oxtotalordersum'], $dbData[$i]['oxdetails']);
                    $i++;
		}
		
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;
	}

        
        function getVoucherOverview()
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
				
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		//$siteCurrency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));

                $db = openDB($this);
		
		$sql = "SELECT v.oxvouchernr, s.oxseriedescription, s.oxdiscount, s.oxdiscounttype, "
                            . "COUNT(*) AS totalcount, SUM(IF(v.oxdateused='0000-00-00',0,1)) AS usedcount, "
                            . "(TO_DAYS(MIN(v.oxdateused))-TO_DAYS(MAX(v.oxdateused))+1) AS days, "
                            . "SUM(o.oxtotalordersum) AS ordersum, AVG(o.oxtotalordersum) AS average "
                        . "FROM oxvouchers v "
                            . "LEFT JOIN oxorder o "
                                . "ON (v.oxorderid = o.oxid "
                                . "AND o.oxshopid = {$this->ShopID[$this->SiteID]}) "
                            . "LEFT JOIN oxvoucherseries s "
                                . "ON v.oxvoucherserieid = s.oxid "
                        . "GROUP BY v.oxvouchernr "
                        . "ORDER BY ordersum DESC "
                        . "LIMIT 0, 500 "; 
		if ($this->DebugMode) logfile('debug', $sql);
                $stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);

                $i = 0;
		foreach($dbData as $value) {
                    $dbData[$i]['oxvouchernr'] = addTitle($dbData[$i]['oxvouchernr'], $dbData[$i]['oxseriedescription']);
                    $dbData[$i]['oxdiscount'] = currFormat($dbData[$i]['oxdiscount'], $this);
                    $dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
                    $dbData[$i]['average'] = currFormat($dbData[$i]['average'], $this);
                    $i++;
		}
		                
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;
	}

        
        function getOpenCashOnDelivery()
	{
            include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
            $this->SiteID = Piwik_Common::getRequestVar('idSite');
            
            return $this->getOpenPayments( $this->PaymentCOD[$this->SiteID] );
        }

        
        function getOpenPayInAdvance()
	{
            include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
            $this->SiteID = Piwik_Common::getRequestVar('idSite');
            
            return $this->getOpenPayments( $this->PaymentCIA[$this->SiteID] );
        }

        
        function getOpenInvoices()
	{
            include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
            $this->SiteID = Piwik_Common::getRequestVar('idSite');
            
            return $this->getOpenPayments( $this->PaymentInvoice[$this->SiteID] );
        }
        
        
        function getOpenPayments($paymentType)
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
				
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                
		$db = openDB($this);

                if (($paymentType == $this->PaymentInvoice[$this->SiteID]) || ($paymentType == $this->PaymentCOD[$this->SiteID])) {
                    $sentWhere = "AND oxsenddate != '0000-00-00 00:00:00' ";
                    $startDate = "oxsenddate";
                }
                else {
                    $sentWhere = "";
                    $startDate = "oxorderdate";
                }
		if ($this->DebugMode) logfile('debug', 'getOpenPayments/'.$paymentType);
		$sql = "SELECT oxid, oxordernr, oxtotalordersum, CONCAT('<nobr>', oxbillfname, ' ', oxbilllname, '</nobr>') AS oxbillname, oxbillemail, "
                         . "CONCAT('<nobr>', oxbillzip, ' ', oxbillcity, '</nobr>') AS oxbilladdress, (TO_DAYS(NOW())-TO_DAYS($startDate)) AS days "
			 . "FROM oxorder "
                         . "WHERE oxpaymenttype = {$paymentType} "
                         . $sentWhere
                         . "AND oxshopid = {$this->ShopID[$this->SiteID]} "
			 . "AND oxpaid = '0000-00-00 00:00:00' "
			 . "AND oxstorno = 0 "
			 . "ORDER BY days ASC " 
			 . "LIMIT 0, 500 "; 
		if ($this->DebugMode) logfile('debug', $sql);
                $stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);

		$i = 0;
		foreach($dbData as $value) {
                    $sql = 'SELECT oxamount, oxtitle '
                            . 'FROM oxorderarticles '
                            . 'WHERE oxorderid = \''.$dbData[$i]['oxid'].'\' '
                                . 'AND oxstorno = 0 ';
                    $stmt = $db->prepare($sql);
                    $stmt->execute();
                    $details = $stmt->fetchAll(PDO::FETCH_NAMED);
                    $dbData[$i]['oxdetails'] = Piwik_Translate('OxidPlugin_Order') . ':';
                    foreach ($details as $detail) {
                        $dbData[$i]['oxdetails'] = $dbData[$i]['oxdetails'] . chr(13) . $detail['oxamount'] . ' x ' . $detail['oxtitle'];
                    }
                    //$dbData[$i]['oxdetails'] = $details;

                    $dbData[$i]['oxtotalordersum'] = currFormat($dbData[$i]['oxtotalordersum'], $this);
                    /*$dbData[$i]['days'] = addTitle($dbData[$i]['days'], $dbData[$i]['oxdetails']);
                    $dbData[$i]['oxordernr'] = addTitle($dbData[$i]['oxordernr'], $dbData[$i]['oxdetails']);*/
                    $dbData[$i]['oxtotalordersum'] = addTitle($dbData[$i]['oxtotalordersum'], $dbData[$i]['oxdetails']);
                    /*$dbData[$i]['oxbillname'] = addTitle($dbData[$i]['oxbillname'], $dbData[$i]['oxdetails']);
                    $dbData[$i]['oxbilladdress'] = addTitle($dbData[$i]['oxbilladdress'], $dbData[$i]['oxdetails']);*/
                    $i++;
		}
                
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;
	}

        
        function getLiveRevenue()
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
				
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                
		$db = openDB($this);
		
		$sql = "SELECT o.oxid AS oxorderid, o.oxordernr, IF(DATE(NOW())=DATE(o.oxorderdate),1,0) AS actualdate, TIME(o.oxorderdate) AS ordertime, "
                            . "CONCAT('<nobr>',DATE(o.oxorderdate),'</nobr>') AS orderdate, DAYOFMONTH(o.oxorderdate) AS orderday,"
                            . "(SELECT COUNT(*) FROM oxorder o2 WHERE o2.oxuserid=o.oxuserid AND o2.oxstorno=0) AS ordercount, "
                            . "CONCAT(o.oxbillfname, '&nbsp;', o.oxbilllname, "
                                . "IF( (SELECT COUNT(*) FROM oxorder o2 WHERE o2.oxuserid=o.oxuserid AND o2.oxstorno=0)=1, "
                                    . "'', "
                                    . "CONCAT('&nbsp;(', (SELECT COUNT(*) FROM oxorder o2 WHERE o2.oxuserid=o.oxuserid AND o2.oxstorno=0), ')') "
                                    . ") "
                                . ") "
                                . "AS oxbillname, "
                            . "p.oxdesc AS paydesc, o.oxtotalordersum, "
                            . "CONCAT(o.oxbillstreet, '&nbsp;', o.oxbillstreetnr, CHAR(13), o.oxbillzip, '&nbsp;', o.oxbillcity) AS oxbilladdress "
                        . "FROM oxorder o, oxpayments p "
                        . "WHERE o.oxstorno = 0 "
                            . "AND o.oxpaymenttype = p.oxid "
                            . "AND o.oxshopid = {$this->ShopID[$this->SiteID]} "
                        . "ORDER BY o.oxordernr DESC "
                        . "LIMIT 0, 50";
		if ($this->DebugMode) logfile('debug', $sql);
                $stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);

		$i = 0;
		foreach($dbData as $value) {
                    $sql = 'SELECT oxamount, oxtitle '
                            . 'FROM oxorderarticles '
                            . 'WHERE oxorderid = \''.$dbData[$i]['oxorderid'].'\' '
                                . 'AND oxstorno = 0 ';
                    $stmt = $db->prepare($sql);
                    $stmt->execute();
                    $details = $stmt->fetchAll(PDO::FETCH_NAMED);
                    $dbData[$i]['oxdetails'] = Piwik_Translate('OxidPlugin_Order') . ':';
                    foreach ($details as $detail) {
                        $dbData[$i]['oxdetails'] = $dbData[$i]['oxdetails'] . chr(13) . $detail['oxamount'] . ' x ' . $detail['oxtitle'];
                    }
                    $dbData[$i]['oxdetails'] = $dbData[$i]['oxdetails'] . chr(13) . '[' . $dbData[$i]['paydesc'] . ']';

                    $dbData[$i]['oxtotalordersum'] = currFormat($dbData[$i]['oxtotalordersum'], $this);
                    //$dbData[$i]['ordertime'] = addTitle($dbData[$i]['ordertime'], $dbData[$i]['orderdate']);
                    $dbData[$i]['oxbillname'] = addTitle($dbData[$i]['oxbillname'], $dbData[$i]['oxbilladdress']);
                    $dbData[$i]['oxtotalordersum'] = addTitle($dbData[$i]['oxtotalordersum'], $dbData[$i]['oxdetails']);
                    if ($dbData[$i]['actualdate'] == 0) {
                        $dbData[$i]['oxbillname'] = showGray($dbData[$i]['oxbillname'], $this);
                        $dbData[$i]['orderdate'] = showGray($dbData[$i]['orderdate'], $this);
                        $dbData[$i]['ordertime'] = showGray($dbData[$i]['ordertime'], $this);
                        $dbData[$i]['oxtotalordersum'] = showGray($dbData[$i]['oxtotalordersum'], $this);
                    }
                    $i++;
		}
                
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;
	}

        
        function getNewsRegs()
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
				
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                
		$db = openDB($this);
                
                $txtDenied = '<nobr><img src="plugins/OxidPlugin/images/NewsInfoRed.jpg" alt="'.Piwik_Translate('OxidPlugin_NewsDenied').'" />'
                            . Piwik_Translate('OxidPlugin_NewsDenied').'</nobr>';
                $txtConfirmed = '<nobr><img src="plugins/OxidPlugin/images/NewsInfoGreen.jpg" alt="'.Piwik_Translate('OxidPlugin_NewsAllowed').'" />'
                            . Piwik_Translate('OxidPlugin_NewsAllowed').'</nobr>';
                $txtRequested = '<nobr><img src="plugins/OxidPlugin/images/NewsInfoAmber.jpg" alt="'.Piwik_Translate('OxidPlugin_NewsNotConfirmeded').'" />'
                            . Piwik_Translate('OxidPlugin_NewsNotConfirmed').'</nobr>';
		
		$sql = "SELECT (TO_DAYS(NOW())-TO_DAYS(s.oxsubscribed)) AS oxdays, s.oxsubscribed, "
                        . "s.oxemail, CONCAT(s.oxfname, '&nbsp;', s.oxlname) AS oxname, "
                        . "CONCAT(u.oxstreet, '&nbsp;', u.oxstreetnr, CHAR(13), u.oxzip, '&nbsp;', u.oxcity) AS oxaddress, "
                        . "(CASE s.oxdboptin "
                            . "WHEN 0 THEN '$txtDenied' "
                            . "WHEN 1 THEN '$txtConfirmed' "
                            . "WHEN 2 THEN '$txtRequested' END) AS oxdboptin, "
                        . "(SELECT SUM(o.oxtotalordersum) "
                            . "FROM oxorder o "
                            . "WHERE o.oxuserid = u.oxid AND o.oxstorno = 0) "
                            . "AS ordersum, "
                        . "(SELECT COUNT(*) "
                            . "FROM oxorder o "
                            . "WHERE o.oxuserid = u.oxid AND o.oxstorno = 0) "
                            . "AS ordercount "
                        . "FROM oxnewssubscribed s, oxuser u "
                        . "WHERE s.oxdboptin != 0 "
                            . "AND s.oxuserid = u.oxid "
                            . "AND u.oxshopid = {$this->ShopID[$this->SiteID]} "
                        . "ORDER BY (TO_DAYS(NOW())-TO_DAYS(s.oxsubscribed)) "
                        . "LIMIT 0, 500";
		if ($this->DebugMode) logfile('debug', 'getNewsRegs()');
		if ($this->DebugMode) logfile('debug', $sql);
                $stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);

                $i = 0;
		foreach($dbData as $value) {
                    /*
                    $sql = 'SELECT oxamount, oxtitle '
                            . 'FROM oxorderarticles '
                            . 'WHERE oxorderid = \''.$dbData[$i]['oxorderid'].'\' '
                                . 'AND oxstorno = 0 ';
                    $stmt = $db->prepare($sql);
                    $stmt->execute();
                    $details = $stmt->fetchAll(PDO::FETCH_NAMED);
                    $dbData[$i]['oxdetails'] = Piwik_Translate('OxidPlugin_Order') . ':';
                    foreach ($details as $detail) {
                        $dbData[$i]['oxdetails'] = $dbData[$i]['oxdetails'] . chr(13) . $detail['oxamount'] . ' x ' . $detail['oxtitle'];
                    }
                    $dbData[$i]['oxdetails'] = $dbData[$i]['oxdetails'] . chr(13) . '[' . $dbData[$i]['paydesc'] . ']';
                    */

                    $dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
                    if ($dbData[$i]['ordercount'] == 1)
                        $orderInfo = $dbData[$i]['ordercount'] . '&nbsp;' . Piwik_Translate('OxidPlugin_Order');
                    else
                        $orderInfo = $dbData[$i]['ordercount'] . '&nbsp;' . Piwik_Translate('OxidPlugin_Orders');
                    $dbData[$i]['ordersum'] = addTitle($dbData[$i]['ordersum'], $orderInfo);
                    $dbData[$i]['ordercount'] = intFormat($dbData[$i]['ordercount'], $this);
                    //$dbData[$i]['oxbillname'] = addTitle($dbData[$i]['oxbillname'], $dbData[$i]['oxbilladdress']);
                    //$dbData[$i]['oxtotalordersum'] = addTitle($dbData[$i]['oxtotalordersum'], $dbData[$i]['oxdetails']);
                    $i++;
		}
                
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;
	}

        
        function getBirthdayUsers()
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
				
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                
		$db = openDB($this);
                
                $txtDenied = '<img src="plugins/OxidPlugin/images/NewsInfoRed.jpg" alt="'.Piwik_Translate('OxidPlugin_NewsDenied').'" />';
                $txtConfirmed = '<img src="plugins/OxidPlugin/images/NewsInfoGreen.jpg" alt="'.Piwik_Translate('OxidPlugin_NewsAllowed').'" />';
                $txtRequested = '<img src="plugins/OxidPlugin/images/NewsInfoAmber.jpg" alt="'.Piwik_Translate('OxidPlugin_NewsNotConfirmed').'" />';
		
		$sql = "SELECT CONCAT("
                                . "(CASE s.oxdboptin "
                                    . "WHEN 0 THEN '$txtDenied' "
                                    . "WHEN 1 THEN '$txtConfirmed' "
                                    . "WHEN 2 THEN '$txtRequested' END), "
                                . "u.oxfname, '&nbsp;', u.oxlname) AS username, "
                            . "u.oxusername, u.oxbirthdate, YEAR(CURDATE())-YEAR(u.oxbirthdate) AS userage, "
                            . "(SELECT COUNT(*) FROM oxorder o2 WHERE o2.oxuserid=u.oxid) AS ordercount, "
                            . "SUM(o.oxtotalordersum) AS ordersum "
			 . "FROM oxuser u "
                            . "LEFT JOIN oxnewssubscribed s ON s.oxuserid = u.oxid "
                            . "LEFT JOIN oxorder o ON o.oxuserid = u.oxid "
			 . "WHERE MONTH(u.oxbirthdate) = MONTH(CURDATE()) "
                            . "AND DAY(u.oxbirthdate) = DAY(CURDATE()) "
                            . "AND u.oxshopid = {$this->ShopID[$this->SiteID]} "
                         . "GROUP BY u.oxid "
                         . "LIMIT 0, 500";
		if ($this->DebugMode) logfile('debug', 'getBirthdayUsers()');
		if ($this->DebugMode) logfile('debug', $sql);
                $stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);

                if ($this->DebugMode) logfile('debug', $dbData);
                $i = 0;
		foreach($dbData as $value) {
                    /*
                    $sql = 'SELECT oxamount, oxtitle '
                            . 'FROM oxorderarticles '
                            . 'WHERE oxorderid = \''.$dbData[$i]['oxorderid'].'\' '
                                . 'AND oxstorno = 0 ';
                    $stmt = $db->prepare($sql);
                    $stmt->execute();
                    $details = $stmt->fetchAll(PDO::FETCH_NAMED);
                    $dbData[$i]['oxdetails'] = Piwik_Translate('OxidPlugin_Order') . ':';
                    foreach ($details as $detail) {
                        $dbData[$i]['oxdetails'] = $dbData[$i]['oxdetails'] . chr(13) . $detail['oxamount'] . ' x ' . $detail['oxtitle'];
                    }
                    $dbData[$i]['oxdetails'] = $dbData[$i]['oxdetails'] . chr(13) . '[' . $dbData[$i]['paydesc'] . ']';
                    */

                    $dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
                    /*****
                    $dbData[$i]['ordersum'] = currFormat($dbData[$i]['ordersum'], $this);
                    if ($dbData[$i]['ordercount'] == 1)
                        $orderInfo = $dbData[$i]['ordercount'] . '&nbsp;' . Piwik_Translate('OxidPlugin_Order');
                    else
                        $orderInfo = $dbData[$i]['ordercount'] . '&nbsp;' . Piwik_Translate('OxidPlugin_Orders');
                    $dbData[$i]['ordersum'] = addTitle($dbData[$i]['ordersum'], $orderInfo);
                    *****/
                    
                    //$dbData[$i]['oxbillname'] = addTitle($dbData[$i]['oxbillname'], $dbData[$i]['oxbilladdress']);
                    //$dbData[$i]['oxtotalordersum'] = addTitle($dbData[$i]['oxtotalordersum'], $dbData[$i]['oxdetails']);
                    $i++;
		}
                
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;  
	}

        
        function getRatingsAndReviews()
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
				
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                
		$db = openDB($this);
                
		$sql1 = "SELECT TO_DAYS(NOW())-TO_DAYS(r.oxcreate) AS oxdays, r.oxcreate AS oxcreate, "
                        . "IF(a.oxparentid = '', "
                            . "a.oxtitle, "
                            . "(SELECT a2.oxtitle FROM oxarticles a2 WHERE a2.oxid = a.oxparentid)) "
                            . "AS oxtitle, "
                        . "a.oxartnum, a.oxprice, "
                        . "CONCAT(SUBSTRING(r.oxtext, 1, 80), IF(CHAR_LENGTH(r.oxtext)>80,'...','')) AS shorttext, "
                        . "r.oxtext AS oxtext, CONCAT('<img src=\"plugins/OxidPlugin/images/rateds', r.oxrating, '.png\">') AS oxrating, "
                        . "CONCAT(u.oxfname, '&nbsp;', u.oxlname, CHAR(13), u.oxstreet, '&nbsp;', u.oxstreetnr, CHAR(13), u.oxzip, 'nbsp;', u.oxcity) AS name, u.oxusername AS oxemail "
                        //. "u.oxlname, u.oxusername AS oxemail, u.oxstreet, u.oxstreetnr, u.oxzip, u.oxcity "
                      . "FROM oxreviews r, oxuser u, oxarticles a "
                      . "WHERE r.oxuserid = u.oxid "
                        . "AND a.oxid = r.oxobjectid "
                        . "AND u.oxshopid = {$this->ShopID[$this->SiteID]} "
                      . "ORDER BY (TO_DAYS(NOW())-TO_DAYS(r.oxcreate)) "
                      . "LIMIT 0, 500 ";
                
                
                if ($this->DebugMode) logfile('debug', 'getRatingsAndReviews()');
		if ($this->DebugMode) logfile('debug', $sql1);
                $stmt = $db->prepare($sql1);
		$stmt->execute();
		$dbData1 = $stmt->fetchAll(PDO::FETCH_NAMED);

                $sql2 = "SELECT TO_DAYS(NOW())-TO_DAYS(r.oxtimestamp) AS oxdays, r.oxtimestamp AS oxcreate, "
                        . "IF(a.oxparentid = '', "
                            . "a.oxtitle, "
                            . "(SELECT a2.oxtitle FROM oxarticles a2 WHERE a2.oxid = a.oxparentid)) "
                            . "AS oxtitle, "
                        . "a.oxartnum, a.oxprice, "
                        . " '-' AS shorttext, '' AS oxtext, CONCAT('<img src=\"plugins/OxidPlugin/images/rateds', r.oxrating, '.png\">') AS oxrating, "
                        . "CONCAT(u.oxfname, '&nbsp;', u.oxlname, CHAR(13), u.oxstreet, '&nbsp;', u.oxstreetnr, CHAR(13), u.oxzip, 'nbsp;', u.oxcity) AS name, u.oxusername AS oxemail "
                      . "FROM oxratings r, oxuser u, oxarticles a "
                      . "WHERE r.oxuserid = u.oxid "
                        . "AND a.oxid = r.oxobjectid "
                        . "AND (SELECT COUNT(*) "
                            . "FROM oxreviews r1 "
                            . "WHERE r1.oxcreate = r.oxtimestamp "
                                . "AND r1.oxuserid = r.oxuserid ) = 0 "
                      . "ORDER BY (TO_DAYS(NOW())-TO_DAYS(r.oxtimestamp)) "
                      . "LIMIT 0, 500 ";
		$stmt = $db->prepare($sql2);
		$stmt->execute();
		$dbData2 = $stmt->fetchAll(PDO::FETCH_NAMED);
                
		foreach($dbData2 as $value) {
                    array_push($dbData1, $value);
                }
		if ($this->DebugMode) logfile('debug', $dbData1);
                
                $i = 0;
		foreach($dbData1 as $value) {
                    $dbData1[$i]['shorttext'] = addTitle($dbData1[$i]['shorttext'], $dbData1[$i]['oxtext']);
                    $dbData1[$i]['oxrating'] = addTitle($dbData1[$i]['oxrating'], $dbData1[$i]['name']);
                    $i++;
                }
                
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData1);
		
		$db = null;
		
		return $dataTable;  
	}

        
        function getPayTypeSums()
	{
		include PIWIK_INCLUDE_PATH . '/plugins/OxidPlugin/conf/'.'config.inc.php';
				
                $this->SiteID = Piwik_Common::getRequestVar('idSite');
		$this->Currency = Piwik::getCurrency(Piwik_Common::getRequestVar('idSite'));
                
		$db = openDB($this);
                
                $selectedYear = date("Y", strtotime(Piwik_Common::getRequestVar('date')));
                $sql = "SELECT sum(oxtotalordersum) AS totalsum "
			. "FROM oxorder "
			. "WHERE YEAR(oxorderdate)=$selectedYear AND oxstorno=0 "
                        . "AND oxshopid = {$this->ShopID[$this->SiteID]} "
			. "GROUP BY YEAR(oxorderdate) ";

                if ($this->DebugMode) logfile('debug', 'getPayTypeSums()');
		if ($this->DebugMode) logfile('debug', $sql);
                $stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);
		$totalSum = $dbData[0]['totalsum'];
		if ($this->DebugMode) logfile('debug', $dbData);

		$sql = "SELECT p.oxdesc, COUNT(*) AS ordercount, SUM(o.oxtotalordersum) AS totalordersum, (SUM(o.oxtotalordersum)/$totalSum*100.0) AS percentage "
			 . "FROM oxorder o, oxpayments p " 
			 . "WHERE p.oxid=o.oxpaymenttype AND p.oxactive=1 AND YEAR(o.oxorderdate)=$selectedYear AND o.oxstorno=0 "
                         . "AND o.oxshopid = {$this->ShopID[$this->SiteID]} "
			 . "GROUP BY p.oxdesc "
			 . "ORDER BY totalordersum DESC "; 
		if ($this->DebugMode) logfile('debug', $sql);
                $stmt = $db->prepare($sql);
		$stmt->execute();
		$dbData = $stmt->fetchAll(PDO::FETCH_NAMED);

                $i = 0;
		foreach($dbData as $value) {
                    $dbData[$i]['totalordersum'] = currFormat($dbData[$i]['totalordersum'], $this);
                    $dbData[$i]['percentage'] = percFormat($dbData[$i]['percentage'], $this);
                    $i++;
		}
                
		$dataTable = new Piwik_DataTable();
		// convert this array to a DataTable object
		$dataTable->addRowsFromArrayWithIndexLabel($dbData);
		
		$db = null;
		
		return $dataTable;  
	}
	
}